CODEWITHSUNDEEP
sqlpostgresql
user1187968
user1187968

Reputation: 8006

Postgres SQL: getting group count

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?

Upvotes: 1

Views: 45

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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.

Upvotes: 2

Related Questions