计算列中的所有行，并在结果中获取2个不同的计数列，即使没有要计数的记录

What I want to do is to count all rows from a column State, and get 2 different count columns on the result (one for each possible value 1 or 0).

我要做的是从列状态中计数所有的行，并在结果中获得两个不同的计数列(每个可能的值为1或0)。

The thing is that I need to show on the results 0 even if there are not records to count.

问题是，我需要在结果0上显示，即使没有记录要计数。

For example I have this table subcategory with this sample data:

例如，我有这个表子类别和这个示例数据:

  SubcategoryID |   Name    |
  ---------------------------
        201     |   Name1   |
        202     |   Name2   |
        203     |   Name3   |
        204     |   Name4   |

And I have this table product with this sample data:

我有这个表格产品和这个样本数据

  ProductID |Subcategory| State |
  -------------------------------
    101     |   201     |   1   |
    102     |   201     |   1   |
    103     |   201     |   1   |
    104     |   202     |   0   |
    105     |   202     |   0   |
    106     |   203     |   1   |
    107     |   203     |   0   |
    108     |   203     |   0   |

State: 1 = Active, 0 = Inactive

状态:1 =活动，0 =不活动。

So I want to get this:

我想要得到这个

|Subcategory| Active|Inactive|
------------------------------
|   201     |   3   |   0    |
|   202     |   0   |   2    |
|   203     |   1   |   2    |
|   204     |   0   |   0    |*

Please note that there are not products related to SubcategoryId = 204 on product table.

请注意，产品表中没有与SubcategoryId = 204相关的产品。

I'm using this query to get only the products that are related to one subcategory. Do you know what I have to add to get the line "|204|0|0|" ?

我使用这个查询只获取与一个子类别相关的产品。你知道我要添加什么东西来得到“|204 bbbbbbbbbbb|”吗?

select p.subcategory,
       sum(case when state = 1 then 1 else 0 end) as active,
       sum(case when state = 0 then 1 else 0 end) as inactive
from product p
group by p.subcategory

2 个解决方案

#1

Use Left Join to get all the rows from subcategory table and then do the Count.

使用左连接从子类别表中获取所有行，然后进行计数。

SELECT S.subcategoryID,
       Sum(CASE WHEN state = 1 THEN 1 ELSE 0 END) AS active,
       Sum(CASE WHEN state = 0 THEN 1 ELSE 0 END) AS inactive
FROM   subcategory s
       LEFT JOIN product p
              ON s.SubcategoryID = p.Subcategory
GROUP  BY s.subcategoryID 

Or use Count, case statement else part can be avoided

或者使用计数，case语句else部分可以避免

SELECT S.subcategoryID,
       count(CASE WHEN state = 1 THEN 1 END) AS active,
       count(CASE WHEN state = 0 THEN 1 END) AS inactive
FROM   subcategory s
       LEFT JOIN product p
              ON s.SubcategoryID = p.Subcategory
GROUP  BY s.subcategoryID 

SQLFIDDLE DEMO

#2

Just another way of doing it.

这是另一种方法。

--Getting the counts for 'active' and 'inactive' states
SELECT S.subcategoryID,
       count(CASE WHEN state = 1 THEN 1 END) AS active,
       count(CASE WHEN state = 0 THEN 1 END) AS inactive
FROM   subcategory s
       JOIN product P ON s.SubcategoryID = p.Subcategory
GROUP  BY s.subcategoryID 

--Now adding subcategories which are only in subcategory table using `EXCEPT`.
UNION ALL
(
SELECT subcategoryid, 0, 0 from subcategory
EXCEPT
SELECT subcategory, 0, 0 from product
)