如何添加具有多个where条件的sum？

hgncfbus 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(335)

我目前面临着一个问题，与多个条件，我正试图做这个条件。

SUM(IF SUM(B.answer) is lesser than 5), 
SUM(IF SUM(B.answer) is between 6-10), 
SUM(IF SUM(B.answer) is between 11-15), 
SUM(IF SUM(B.answer) is greater than 16)
GROUP BY A.schlvl

期待这样的结果：

希望有人能理解我的解释

SELECT DISTINCT A.schlvl AS school, COUNT(DISTINCT(A.name)) AS NoOfChild, CASE WHEN SUM(B.answer)<5 THEN SUM(B.answer) END FROM child AS A INNER JOIN question AS B ON A.child_id = B.child_id 
WHERE SUM(B.answer) IN (<5,>=6 && <=10,>=11 && <=15,>=16)
GROUP BY A.schlvl

sql mysql sum where

来源：https://stackoverflow.com/questions/51693392/how-do-i-add-sum-with-multiple-where-condition

2条答案

按热度按时间

hm2xizp91#

不要在case when中使用sum，你可以做的是在case when中求和。
然而在这种情况下，当看到预期结果的截图时？
我想它需要伯爵来代替。
例子：

SELECT
A.schlvl AS [School Level],
COUNT(DISTINCT(A.name)) AS [Total No. Of Children], 
COUNT(DISTINCT CASE WHEN B.sum_answer <= 5 THEN A.name END) AS [le 5],
COUNT(DISTINCT CASE WHEN B.sum_answer between 6 and 10 THEN A.name END) AS [6 to 10],
COUNT(DISTINCT CASE WHEN B.sum_answer between 11 and 15 THEN A.name END) AS [11 to 15],
COUNT(DISTINCT CASE WHEN B.sum_answer >= 16 THEN A.name END) AS [ge 16]
FROM child AS A 
LEFT JOIN 
(
   SELECT child_id, SUM(answer) AS sum_answer
   FROM question
   GROUP BY child_id
) AS B ON A.child_id = B.child_id
GROUP BY A.schlvl

此外，如果需要对聚合（f.e.sum，count）进行限制，那么可以在having子句中进行限制。
我现在认为这里不需要。
自从得到了 <=5 or between 6 and 15 or >=16 几乎没有限制。你都需要吗？

赞(0）回复(0）举报 2021-06-20

bbmckpt72#

如果需要对聚合结果进行筛选，则必须使用having而不是where，并且应该使用有效的or子句而不是range的in子句

SELECT DISTINCT A.schlvl AS school
      , COUNT(DISTINCT(A.name)) AS NoOfChild
      , CASE WHEN SUM(B.answer)<5 THEN SUM(B.answer) END 
  FROM child AS A 
  INNER JOIN question AS B ON A.child_id = B.child_id 
  HAVING SUM(B.answer) IN (<5,>=6 && <=10,>=11 && <=15,>=16)
  HAVING SUM(B.answer) < 5 
          OR (SUM(B.answer) >=6 AND SUM(B.answer) <=10) 
          OR (SUM(B.answer) >=11 AND SUM(B.answer) <=15)
          OR (SUM(B.answer) >=16)
  GROUP BY A.schlvl

赞(0）回复(0）举报 2021-06-20

我来回答

如何添加具有多个where条件的sum？

2条答案

相关问题

热门标签

最新问答