MYSQL - Multiple counts in a single query

Question

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    |

Jason Heo · Accepted Answer · 2014-01-29 06:08:01Z

6

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

edited Jan 29, 2014 at 6:08

answered Jan 29, 2014 at 5:54

Jason Heo

10.3k2 gold badges39 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ravinder Reddy Over a year ago

And, just define column aliases as as '> 50' and as '< 50'

ScayTrase Over a year ago

Just SUM(cpu_usage > 50), no IF, because cpu_usage > 50 casts to 1 or 0 by itself.

Collectives™ on Stack Overflow

MYSQL - Multiple counts in a single query

1 Answer 1

Updated

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Updated

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related