MySQL聚合查询？

Question

我有以下 mySQL 代码:

SELECT 
      c.categoryId, 
      c.categoryName, c.categoryParent, 
      c.categoryDescription, 
      COUNT(p.productid) as totalProdsInCategory
    FROM categories as c
     LEFT JOIN normalproducts as p 
     ON c.categoryId = p.categoryid
WHERE c.categoryId = 41
    GROUP BY c.categoryId
    ORDER BY c.categoryParent ASC,  c.categoryName ASC

我希望能够包含另一个 COUNT 列。但这只能算作 p.state = "active"的产品。这是我不正确的解决方案

SELECT 
      c.categoryId, 
      c.categoryName, c.categoryParent, 
      c.categoryDescription, 
      COUNT(p.productid) as totalProdsInCategory, 
      COUNT(q.productid) as totalActiveProdsInCategory
    FROM categories as c
     LEFT JOIN normalproducts as p 
     ON c.categoryId = p.categoryid
WHERE c.categoryId = 41
    GROUP BY c.categoryId
    ORDER BY c.categoryParent ASC,  c.categoryName ASC

有什么帮助吗？我不知道从这里去哪里......

Answer 1

您可以使用 SUM (CASE WHEN p.state = "active"THEN 1 ELSE 0 END) 来获取您要查找的计数。

SELECT 
      c.categoryId, 
      c.categoryName, c.categoryParent, 
      c.categoryDescription, 
      COUNT(p.productid) as totalProdsInCategory, 
      SUM (CASE WHEN p.state = "active" THEN 1 ELSE 0 END) totalActiveProdsInCategory
    FROM categories as c
     LEFT JOIN normalproducts as p 
     ON c.categoryId = p.categoryid
WHERE c.categoryId = 41
    GROUP BY c.categoryId
    ORDER BY c.categoryParent ASC,  c.categoryName ASC