sql to receive all information using count

user_8936041 0
Viewed 33

I have a table called products and has attributes code, type(tablet, desktop, phone), and manufacturer.

I want to get the information of the number of tablets each manufacturer makes.

select manufacturer, count(manufacturer) 
from products 
where type = "tablet" 
group by manufacturer

This query only returns the manufacturers which have at least a tablet. I would like to also print manufacturers which have 0 tablets.

For example: (manufacturer, type)

  1. (Samsung, tablet)
  2. (Apple, tablet)
  3. (Dell, phone)
  4. (Apple, tablet)

Then I wish to get

Samsung: 1
Apple: 2
Dell: 0
sql
2 Answers

You can use conditional aggregation:

select p.manufacturer, sum(case when p.type = 'tablet' then 1 else 0 end)
from products p
group by p.manufacturer
user_11440350

in PostgreSQL notation:

select manufacturer, COUNT(*)filter(WHERE type = 'tablet')
from products
group by manufacturer
user_120815430
Related