Reputation: 67
I'm trying to do multiple counts in a single query but still not getting the results I want. There are 6 servers on 5F / 6F and I want to count how many servers on 5F / 6F are using CPU more than 50% or how many servers on 5F / 6F are using CPU less than 50%.
I tried to send the below query but the result is not separated by group.
SELECT room, (SELECT count( > 50) FROM table WHERE empty > 50),
(SELECT count( < 50) FROM table WHERE empty < 50) FROM table GROUP BY location;
Table:
| name | location | CPU usage |
| host1 | 5F | 60 |
| host2 | 5F | 20 |
| host2 | 5F | 80 |
| host3 | 6F | 30 |
| host4 | 6F | 40 |
| host5 | 6F | 90 |
Desired output:
| location | > 50 | < 50 |
| 5F | 2 | 1 |
| 6F | 1 | 2 |
Upvotes: 2
Views: 118
Reputation: 10246
Could you try this?
SELECT location, SUM(IF(cpu_usage > 50, 1, 0)), SUM(IF(cpu_usage <=50, 0, 1))
FROM table
GROUP BY location
As ScayTrase mentioned, cpu_usage > 50
itselfs return 0 or 1 for true or false respectively. So, preceding query could be simplified as follows.
SELECT location, SUM(cpu_usage > 50), SUM(cpu_usage <=50)
FROM table
GROUP BY location
Upvotes: 6