mysql - 带有计数结构的sql查询结果

Question

我不明白，因为这个 SQL 查询不起作用：

SELECT COUNT Department, 
        IF(Department  = 'toys', COUNT(*), 0) AS nt, 
        IF(Department  = 'health', COUNT(*), 0) AS nh
FROM TABLE;

桌子

Department  Value
toys        A
toys        B
toys        C
health      K
health      F
toys        G
toys        R
toys        W
toys        Q

我想将玩具记录和健康记录的出现次数统计为 2 列。

department    nt    nh
toys          7     0
health        0     2

为什么 ？！

谢谢

标签： mysql

Answer 1

SELECT department,
       SUM(IF(Department  = 'toys',1 , 0)) AS nt, 
       SUM(IF(Department  = 'health', 1, 0)) AS nh
FROM TABLE;

这将使您在两列中计数。

如果匹配，它将只返回一个总和。

我想推荐的替代查询

SELECT Department, 
       COUNT(*)
FROM TABLE
WHERE Department IN ('toys','health')
GROUP BY Department;

此查询将为您提供部门明智的计数。