CODEWITHSUNDEEP
postgresqlcountaggregate-functionsknex.js
MikeL
MikeL

Reputation: 208

Postgresql - get counts along with results

I have 2 tables

cars
|id|name|car_type_id|

car_types
|id|name|

car_type_id can be null for a car.

I want to query my db so I can get a list of the car types with the counts of how many cars per car_type. The cars with null in car type can be null.

I hope this was clear. But any advice is welcome.

So I am looking to get back something like

car_type      | count
car type 1    | 3
car type 2    | 4
uncategorized | 12

EDIT: So I can get the types and their totals using

select 
car_type.id,
car_type.name,
(SELECT COUNT(*) FROM cars 
 WHERE cars.car_type_id = car_type.id) as tot
from car_type

But this does not give me all those cars with car_type_id of null

Upvotes: 2

Views: 278

Answers (3)

Bohemian
Bohemian

Reputation: 424993

Use an outer (ie left) join from cars to car_types, but specify 'uncategorized' as the car_type name where there is no join. Union that with a reverse join that filter out matching rows.

select 
    coalesce(car_types.name, 'uncategorized') as car_type,
    count(*) as tot
from cars
left join car_types on cars.car_type_id = car_types.id
group by 1
union
select car_types.name, 0
from car_types
left join cars on cars.car_type_id = car_types.id
where cars.car_type_id is null
union
select * from (select 'uncategorized', 0)
where exists (select * from cars where car_type_id is null)

fyi, coalesce() returns the first non-null value from the list of values provided to it, and all columns are null in a left-joined table when there are no matching rows.

Upvotes: 1

Leo C
Leo C

Reputation: 22439

Just a simple LEFT JOIN and GROUP BY would do it:

SELECT coalesce(t.name, 'uncategorized') car_type, count(*)
FROM cars c LEFT JOIN car_types t ON c.car_type = t.id
GROUP BY t.id;

   car_type    | count 
---------------+-------
 uncategorized |     2
 suv           |     2
 sedan         |     3
(3 rows)

Upvotes: 1

flutter
flutter

Reputation: 754

You can use UNION [ALL] to combine two SELECTs, like 

SELECT 1  
UNION ALL  
SELECT 2

to yield

some_column_name
1
2

So we can add the NULL values separately, like so 

select 
car_type.id,
car_type.name,
(SELECT COUNT(*) FROM cars WHERE cars.car_type_id = car_type.id) as tot
from car_type
UNION ALL  -- changes begin here, the query above is yours
SELECT NULL, 'unknown', (COUNT (*) FROM cars WHERE car_type IS NULL);

Upvotes: 0

Related Questions