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

Question

Here is a very basic look at my table. I have columns 1 and 2 and need to generate column 3. Column 3 is simply the total of the Number column for all Name divided by Number for the given row.

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

I'm struggling with how to get the total of the number row and use this as a value to calculate the rest.

EDIT: I'm looking to do this as a single query instead of two separate if possible.

Answer 1

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

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

Demo Here

Answer 2

If I were doing this, I would start by storing a variable that held the total, like this:

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

Once I had that variable, I could run a query that got the percentage for each row like this:

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

If you don't want to use a variable, you can just move that subquery into your select statement:

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

Here is an SQL Fiddle example with each approach.