MySQL – Grouping and Counting Based on Status and Category ID

countdistinctMySQLselect

I have a table with product ID, category ID and status of the product.

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

How do I group based on its status, then count its product and the total based on its category ID? I'm expecting like this:

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

Where status=1 is published and status=0 is unpublished.

Best Answer

You need conditional aggregation using 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

Edit:

My crystal ball was apparently broken, but now it's fixed :-)

If you need to count each product_id only once you can use this:

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
Related Question