我在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/

10-14 15:15
查看更多