根據狀態分組，然後根據其類別ID計算其產品和總數

我有一個包含產品 ID、類別 ID 和產品狀態的表格。

+---------+-------------+-------------+
|   id    |   cat_id    |   status    |
+---------+-------------+-------------+
|    1    |     1       |      1      |
|    2    |     1       |      1      |
|    3    |     2       |      1      |
|    4    |     2       |      0      |
|    5    |     1       |      0      |
|    6    |     2       |      0      |
+---------+-------------+-------------+

我如何根據其狀態進行分組，然後根據其類別ID計算其產品和總數？我期待這樣：

+---------+-------------+-------------+-----------+
|  cat_id |  published  | unpublished |   totals  |
+---------+-------------+-------------+-----------+
|    1    |     2       |      1      |     3     |
|    2    |     1       |      2      |     3     |
+---------+-------------+-------------+-----------+

在哪里和status=1是。published``status=0``unpublished

您需要使用 sum(case) 進行條件聚合：

select cat_id,
  sum(case when status = 1 then 1 else 0 end) as published,  -- only count status 1
  sum(case when status = 0 then 1 else 0 end) as unpublished,-- only count status 0
  count(*) as totals
from tab
group by cat_id

編輯：

我的水晶球顯然壞了，但現在它已經修好了:-)

如果您只需要計算每個 product_id 一次，您可以使用它：

select cat_id,
  count(distinct case when status = 1 then product_id end) as published,  -- only count status 1
  count(distinct case when status = 0 then product_id end) as unpublished,-- only count status 0
  count(distinct product_id) as totals
from tab join whatever...
group by cat_id

引用自：https://dba.stackexchange.com/questions/102338