Postgres SQL: getting group count

Question

I have the following table

>> tbl_category

id | category
-------------
0  | A
1  | B
...|...

>>tbl_product

id | category_id | product
---------------------------
0  | 0           | P1 
1  | 1           | P2
...|...          | ...

I can use the following query to count the number of products in a category.

select category, count(tbl.product) from tbl_product 
join tbl_category on tbl_product.category_id = category.id 
group by catregory

However, there are some categories that never have any product belonging to. How do I get these to show up in the query result as well?

Gordon Linoff · Accepted Answer · 2017-07-10 18:11:08Z

2

Use a left join:

select c.category, count(tbl.product) 
from tbl_category c left join
     tbl_product p
     on p.category_id = c.id 
group by c.category;

The table where you want to keep all the rows goes first (tbl_category).

Note the use of table aliases to make the query easier to write and to read.

answered Jul 10, 2017 at 18:11

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lemjur Over a year ago

You might add extra coalesce at count(tbl.product) to show 0 at least coalesce(count(tbl.product),0).

Gordon Linoff Over a year ago

@Lemur . . . Utterly unnecessary. COUNT() returns 0, not NULL if there are no matches.

Collectives™ on Stack Overflow

Postgres SQL: getting group count

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related