CODEWITHSUNDEEP
mysql
Elena Politi
Elena Politi

Reputation: 181

MySql select sum and calculate percentage per column

Following a select, where I get sums grouped by ids, I need to calculate also the percentage of each sum with respect to the totals

The MySQL table looks like the following

|status|a|b1|b2|c1|c2|
-------------------------
|IMMA  |1|0 |0 |1 |0 |
|IMMA  |0|1 |1 |0 |1 |
|IMMA  |1|1 |1 |0 |1 |
|IMMA  |1|1 |1 |0 |1 |

The query I use now is

SELECT imma_dbase_full.`status`,
SUM(imma_dbase_full.`a`) as `A`,
SUM(imma_dbase_full.`b1`) + SUM(imma_dbase_full.`b2`) as `B`,
SUM(imma_dbase_full.`c1`) + SUM(imma_dbase_full.`c2`) as `C`
FROM imma_dbase_full
WHERE imma_dbase_full.`status` = 'IMMA'
GROUP BY imma_dbase_full.`status`;

The output is

|status|A|B|C|
----------------
|IMMA  |3|6|3|

However I need to create an output as the following:

|status|A  |B  |C  |
-------------------- 
|IMMA  |3  |6  |3  |
|%     |25%|50%|25%|

Is that possible with MySQL?

Upvotes: 0

Views: 534

Answers (1)

Fenistil
Fenistil

Reputation: 3801

It's possible to do the calculation in a separate SELECT then UNION the two, making it appears as one, but it's not a very good solution. For optimization, it would be better to calculate the percentage at application level.

SELECT `status`,
SUM(`a`) as `A`,
SUM(`b1`+`b2`) as `B`,
SUM(`c1`+`c2`) as `C`
FROM imma_dbase_full
WHERE `status` = 'IMMA'
UNION
SELECT '%',
SUM(`a`) / SUM(`a`+`b1`+`b2`+`c1`+`c2`) * 100,
SUM(`b1`+`b2`) / SUM(`a`+`b1`+`b2`+`c1`+`c2`) * 100,
SUM(`c1`+`c2`) / SUM(`a`+`b1`+`b2`+`c1`+`c2`) * 100
FROM imma_dbase_full
WHERE `status` = 'IMMA'

Ps.: In your original query the GROUP BY makes no sense, since you're filtering on Status=IMMA so there will be only one status, there is no need to group by.

Upvotes: 2

Related Questions