CODEWITHSUNDEEP
mysqlcount
DHKIM
DHKIM

Reputation: 67

MYSQL - Multiple counts in a single query

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

Answers (1)

Jason Heo
Jason Heo

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

Updated

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

Related Questions