CODEWITHSUNDEEP
mysqlsql
user882670
user882670

Reputation:

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

Upvotes: 1

Views: 14313

Answers (3)

John Ruddell
John Ruddell

Reputation: 25842

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

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can do this several ways. One is to just use a subquery in the select clause. As written below, this assumes that the category_id column in table1 always matches categories:

SELECT c.categoria AS "Group", SUM(t1.atual) AS "Sum",
       SUM(t1.atual) / (SELECT SUM(t1.atual) FROM table1) as "Percent"
FROM `table1` t1 INNER JOIN
     categories c
     ON t1.category_id = c.id
GROUP BY c.categoria;

I changed the group by clause as well. It is a good idea for the group by and select to use the same columns. And I added table aliases to all the column references, another good practice.

Upvotes: 1

juergen d
juergen d

Reputation: 204766

SELECT categories.cat AS categoria, 
       SUM(atual) * 100 / (select sum(atual) from table1) AS percentages 
FROM `table1` 
INNER JOIN categories ON table1.category_id=categories.id 
GROUP BY categoria

Upvotes: 2

Related Questions