Nuno Nogueira Nuno Nogueira - 3 months ago 13
MySQL Question

MySQL query to calculate percentage of total column

How to convert this result:

Group | Sum
Services | 11120.99
Vendas | 3738.00


Into:

Group | Sum
Services | 74.84
Vendas | 25.16


That is, the second displays the results as percentages of total.

This is what I tried:

SELECT categories.cat AS 'Group', SUM(atual) AS 'Sum'
FROM `table1` INNER JOIN
categories
ON table1.category_id=categories.id
GROUP BY categoria

Answer

you can left join a total sum that is not grouped or split up, and divide that by your sum query. this way you are just doing the total select once for faster runtime

SELECT cat, sum_atual, sum_atual/total_atual as percent_atual 
FROM
(   SELECT categories.cat AS cat, SUM(atual) AS sum_atual
    FROM `table1` 
    JOIN categories ON table1.category_id=categories.id
    GROUP BY categoria
) t
LEFT JOIN 
(   SELECT SUM(atual) as total_atual
    FROM `table1`
) t1
Comments