sql查询获取count和group by输出值

uklbhaso 于 2021-08-13 发布在 Java

关注(0)|答案(1)|浏览(315)

我有下表。
表名：machine

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

为未满负荷的机器计数编写查询很容易。 SELECT machine_type, count(*) FROM Machine WHERE ROUND((current_usage/max_capacity)*100, 2) < 100.00 GROUP BY machine_type; 但我正在尝试编写sql查询，以以下格式输出数据

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

我可以为每个使用窗口修改我的查询，但那将是三条语句。有没有一种方法可以在单个sql语句中实现这一点？

sql mysql

来源：https://stackoverflow.com/questions/61945515/sql-query-to-get-count-and-group-by-output-values

1条答案

按热度按时间

7uhlpewt1#

可以使用条件聚合：

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;

赞(0）回复(0）举报 2021-08-13

我来回答

sql查询获取count和group by输出值

1条答案

相关问题

热门标签

最新问答