我在PostgreSQL引擎驱动的数据库中有一个表answers
,其中有一个与问题相关联的数字。我需要数一数有多少个数字,有多少数字低于6,按问题分组。
我想要的是:
SELECT question, count(*) AS Qanswers, count_below_6(*) AS Qanswers_below_6
FROM answers
GROUP BY question;
question | Qanswers | Qanswers_below_6
-----------------------+----------+------------------
How do you feel? | 1234 | 53
Was clear the webinar? | 8444 | 20
How much that hurt? | 3666 | 142
现在我在做
SELECT question, count(*) AS Qanswers
FROM answers
GROUP BY question;
然后
SELECT question, count(*) AS Qanswers
FROM answers
WHERE value < 6
GROUP BY question;
之后,我手动合并这两个结果。
我能做一个句子,让我得到我需要的结果吗?
最佳答案
SELECT question
,count(*) AS answers
,count(value < 6 OR NULL) AS under_6
FROM answers
GROUP BY question;
诀窍是
count(expression)
只计算非空值(而count(*)
计算所有行)。这里有一个详细的解释,为什么我们在正确的地方得到空值:Compute percents from SUM() in the same SELECT sql query
要做到这一点,有许多等效的技术。与基准的详细比较:
For absolute performance, is SUM faster or COUNT?
关于sql - 计算总数和部分,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/24091197/