Get the sum of the column and use to calculate the percentage of the total (mySQL)

Question

Here is a very simple look at my table. I have columns 1 and 2 and you need to create column 3. Column 3 is just the Number column for all Name divided by Number for this row.

 | Name | Number | % of total | | ------------- |:-------------:| -----: | | Bob | 5 | 25 | | Sally | 10 | 50 | | John | 5 | 25 | 

I am struggling with how to get the total number of rows and use this as a value to calculate the rest.

EDIT: I am looking to do this as one request instead of two separate if possible.

Answer 1

You need CROSS JOIN SUM() of the Number column:

 SELECT Name, Number, Number * 100 / ts AS `% of total` FROM mytable CROSS JOIN (SELECT SUM(Number) AS s FROM mytable) t 

Demo here

Answer 2

You can get it with a subquery:

 SELECT Name, Number, Number * 100 / (select sum(Number) FROM MYTABLE) AS '% of total' FROM mytable 

Answer 3

If I did this, I would start by storing a variable containing the total amount, for example:

 SET @total := (SELECT SUM(number) FROM myTable); 

Once I had this variable, I could run a query that received a percentage for each row as follows:

 SELECT name, number, (number / @total) * 100 AS percentage FROM myTable; 

If you do not want to use a variable, you can simply move this subquery to your select statement:

 SELECT name, number, (number / (SELECT SUM(number) FROM myTable)) * 100 AS percentage FROM myTable; 

The following is an example SQL Fiddle with each approach.