CODEWITHSUNDEEP
mysqlsql
Bhaskar
Bhaskar

Reputation: 2733

SQL query to get count and group by output values

I have following table.

Table Name: Machine 

id  machine_type    current_usage       max_capacity
1   1               25                  500  
2   1               20                  500  
3   2               300                 400  
...

Writing query for count of machines not at full capacity was easy.

SELECT machine_type, count(*) FROM Machine WHERE ROUND((current_usage/max_capacity)*100, 2) < 100.00 GROUP BY machine_type;

But I'm trying to write SQL query to output data in below format

machine_type    thirty_percent  sixty_percent   more_than_sixty
1               25              40              50
2               40              15              25
3               60              10              55
...

thirty_percent = Number of machines whose usage is <= 30% usage
sixty_percent = Number of machines whose usage is > 30% and <=60% usage
more_than_sixty = Number of machines whose usage is > 60% and < 100% usage

I can modify my query for each window of usage, but that'll be three statements. Is there a way to do this in single SQL statement?

Upvotes: 0

Views: 31

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You can use conditional aggregation:

SELECT machine_type,
       SUM(current_usage / max_capacity < 0.30) as percent_30,
       SUM(current_usage / max_capacity >= 0.3 and current_usage / max_capacity < 0.60) as percent_60,
       SUM(current_usage / max_capacity >= 0.60) as percent_60_plus
FROM Machine
GROUP BY machine_type;

Upvotes: 1

Related Questions