0

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.

5

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.