如何在mysql查询中得到sum的和？

gzszwxb4 于 2021-06-18 发布在 Mysql

关注(0)|答案(5)|浏览(386)

我有sql查询查找apicount和javascriptcount现在我想一个计数为apicount+javascriptcount我怎么能这样做请帮助我。我试图找到许多解决方案在谷歌上，但我不能得到确切的解决我的问题。

SELECT  display , 
  SUM(CASE WHEN category = 2 AND (statusCode != -1 AND statusCode != 400  AND statusCode != 401) THEN 1 ELSE 0 END) apiCount, 
  SUM(CASE WHEN category = 1 THEN 1 ELSE 0 END) javascriptCount 
FROM error 
WHERE  `occurance` >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) 
GROUP BY display

这是我的查询，我想在同一个查询中求apicount和javascriptcount的和。

sql mysql

来源：https://stackoverflow.com/questions/52474032/how-to-get-sum-of-sum-in-mysql-query

5条答案

按热度按时间

tkclm6bt1#

嗨，请尝试以下查询。

SELECT  display , 
  SUM(CASE WHEN 
   (category = 2 AND (statusCode != -1 AND statusCode != 400  AND statusCode != 401)) -- apiCount
   OR (category = 1) -- javascriptCount
    THEN 1 ELSE 0 END) apiCount_and_javascriptCount,
  occurance 
 FROM error 
  WHERE  `occurance` >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) GROUP BY display

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

f4t66c6m2#

在mysql中 SUM 有了条件，条件成立就加一个，否则就不加了。
你可以试试这个。

SELECT display, 
       SUM(category = 2 AND (statusCode != -1 AND statusCode != 400  AND statusCode != 401)) apiCount, 
       SUM(category = 1 ) javascriptCount,
       SUM((category = 2 AND (statusCode != -1 AND statusCode != 400  AND statusCode != 401)) OR category = 1) totalCount,
       occurance 
FROM error 
WHERE  `occurance` >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) 
GROUP BY display

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

kkih6yb83#

将原始查询 Package 到派生表中。然后可以计算apicount+javascriptcount：

select dt.*, apiCount + javascriptCount
from
(
    SELECT  display , 
      SUM(CASE WHEN category = 2 AND (statusCode != -1 AND statusCode != 400  AND statusCode != 401) THEN 1 ELSE 0 END) apiCount, 
      SUM(CASE WHEN category = 1 THEN 1 ELSE 0 END) javascriptCount,
      occurance 
    FROM error 
    WHERE  `occurance` >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) 
    GROUP BY display
) dt

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

iyfamqjs4#

使用“+”运算符使其合计，也可以将这两个逻辑组合起来进行计数

SELECT  display , 
SUM(CASE WHEN category = 2 AND (statusCode != -1 AND statusCode != 400  AND statusCode != 401) THEN 1 ELSE 0 END)+SUM(CASE WHEN category = 1 THEN 1 ELSE 0 END) 
 totalCount,occurance 
 FROM error WHERE  `occurance` >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) GROUP BY display

或者

SELECT  display , 
SUM(CASE WHEN (category i=2 AND (statusCode != -1 AND statusCode != 400  AND statusCode != 401)) OR category = 1 THEN 1 ELSE 0 END)
 totalCount,occurance 
 FROM error WHERE  `occurance` >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) GROUP BY display

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

qlzsbp2j5#

你可以把两者结合起来 CASE 用于获得组合和的表达式：

SELECT  display , 
  SUM(CASE WHEN category = 2 AND (statusCode != -1 AND statusCode != 400  AND statusCode != 401) THEN 1 ELSE 0 END) apiCount, 
  SUM(CASE WHEN category = 1 THEN 1 ELSE 0 END) javascriptCount,
  SUM(CASE WHEN category = 2 AND (statusCode != -1 AND statusCode != 400  AND statusCode != 401) OR category = 1 THEN 1 ELSE 0 END) totalCount, 
  occurance 
FROM error 
WHERE  `occurance` >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) 
GROUP BY display

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

我来回答

如何在mysql查询中得到sum的和？

5条答案

相关问题

热门标签

最新问答