Calculate percentage using values in mysql query output

Question

I am using below query to count two different values in a row and its working perfectly.

Seek your help in finding how I can make the query to show output in percentage values

Query is

SELECT `Machine`,
        SUM(CASE WHEN `state` = 'UnHealthy' THEN 1 ELSE 0 END) 'Red'
     , SUM(CASE WHEN `state` = 'Healthy' THEN 1 ELSE 0 END) 'Green'
     , SUM(CASE WHEN `state` = 'UnHealthy' or `state` = 'Healthy' THEN 1 ELSE 0 END) 'Total'
  FROM Report Where date(`TOI`) >= DATE(NOW()) - INTERVAL 7 DAY
  Group by `Machine`

Output is

Machine Red Green   Total   
Node1   3   14      17  
Node2   0   3       3   
Node3   4   6       10

How can I make the above result to come in percentage, like below

Machine Red Green   Total   
Node1   20% 80%      17 
Node2   25% 75%      3  
Node3   50% 50%     10

Which later I want to bootstrap to PHP as progressbar.

Thanks,

Also, do not use Single quotes for column identifier / aliasnames. Refer: https://stackoverflow.com/a/11321508/2469308 — Madhur Bhaiya, Nov 25 '18 at 13:45
The percentages in your output make no sense. Please clarify this to increase that chance that you will get a meaningful answer here. — Tim Biegeleisen, Nov 25 '18 at 13:48
Sorry for the confusion, the values in the expected table are only as a reference which I have typed in haste. Tim's solution worked perfectly. — Bharath, Nov 25 '18 at 14:15

score 0 · Accepted Answer · answered Nov 25 '18 at 13:51

Here is a fairly tidy way of writing this. We can put most of your current logic into a subquery, and then use the conditional sums to generate the percentages and total which you want.

SELECT
    Machine,
    100.0 * Red / (Red + Green) AS Red,
    100.0 * Green / (Red + Green) AS Green,
    Red + Green AS Total
FROM
(
    SELECT
        Machine,
        COUNT(CASE WHEN state = 'UnHealthy' THEN 1 END) Red,
        COUNT(CASE WHEN state = 'Healthy' THEN 1 END) Green
    FROM Report
    WHERE DATE(TOI) >= DATE(NOW()) - INTERVAL 7 DAY
    GROUP BY Machine
) t
ORDER BY
    Machine;

Note that there is a problem with your current sample data, but this seems to be on track with what you are trying to do. Also, we could have written the above without the subquery, but it would have been much harder to read. If performance be a big concern of yours, we can also give a version without the subquery.

Actually in 5.7+, Optimizer should be able to merge them into one subquery instead. — Madhur Bhaiya, Nov 25 '18 at 13:56

Calculate percentage using values in mysql query output

1 Answers1