I have 3 tables: Vehicle, type and color
Vehicle
id brand type_id color_id
1 Toyota 1 2
2 Toyota 2 3
3 GMC 2 1
4 BMW 2 1
Type
id name
1 Truck
2 Car
Color
id name
1 White
2 Red
3 Black
I want to get the count of each combination of type and color including 0 values, like this:
type.name color.name count
Truck White 0
Truck Red 1
Truck Black 0
Car White 2
Car Red 0
Car Black 1
I tried this query below
SELECT type.name, color.name, count(vehicle.id) count
FROM vehicle
RIGHT JOIN type on vehicle.type_id = type.id
RIGHT JOIN color on color.id = vehicle.color_id
GROUP BY type.name, color.name
but it doesn't produce zero values:
type.name color.name count
Truck Red 1
Car White 2
Car Black 1
Is there a neat query to achieve the first results?
As mention @jarlh in comments, this can be done using CROSS JOIN :
WITH CTE AS (
SELECT t.id as type_id, t.name as type_name, c.id as color_id, c.name as color_name
FROM type t
CROSS JOIN color c
)
SELECT c.type_name, c.color_name, count(t.color_id) as count
FROM CTE c
LEFT JOIN vehicle t on t.type_id = c.type_id and t.color_id = c.color_id
GROUP BY c.type_name, c.color_name
order by c.type_name desc, c.color_name desc
Or simply :
SELECT t.name as type_name, c.name as color_name, count(v.id) as count
FROM type t
CROSS JOIN color c
LEFT JOIN vehicle v on v.type_id = t.id and v.color_id = c.id
GROUP BY t.name, c.name
order by t.name desc, c.name desc
Results :
type_name color_name count
Truck White 0
Truck Red 1
Truck Black 0
Car White 2
Car Red 0
Car Black 1