sql 在一个查询中获取总数和过滤计数 [英] sql get total and filtered count in one query

问题描述

我希望能够说明每个团队中得分超过 10 分的用户百分比.目前这需要两个查询:

I want to be able to tell what percentage of users for each team have more than 10 points. This currently requires two queries:

SELECT COUNT(*) as winners, team FROM users WHERE points > 10 GROUP BY team

SELECT COUNT(*) as total, team FROM users GROUP BY team

我可以一并执行此操作，以便得到如下结果:

Can I do this in one so I get a result like this:

winners, total, team
5, 16, A

推荐答案

您可以使用 Case .. When 检查特定行的 points 是否超过 10，并相应地计数(使用 Sum()).

You can use Case .. When to check if points are more than 10 for a particular row, and count it accordingly (using Sum()).

SELECT COUNT(*) as total, 
       SUM(CASE WHEN points > 10 THEN 1 ELSE 0 END) AS winners, 
       team 
FROM users 
GROUP BY team

<小时>

在 MySQL 中，我们可以进一步缩短为 Sum() 函数可以简单地将条件运算符/函数的结果转换为 0/1(分别为 false/true):

---

In MySQL, we can shorten it further as Sum() function can simply cast results of conditional operators/functions to 0/1 (for false/true respectively):

SELECT COUNT(*) as total, 
       SUM(points > 10) AS winners, 
       team 
FROM users 
GROUP BY team

这篇关于sql 在一个查询中获取总数和过滤计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！