Prueba con algo como esto:
Código SQL:
Ver originalCREATE TABLE #autos
(
auto nvarchar(20),
id_color INT
)
CREATE TABLE #colores
(
id INT,
color nvarchar(20)
)
INSERT INTO #autos VALUES ('auto1',1)
INSERT INTO #autos VALUES ('auto2',1)
INSERT INTO #autos VALUES ('auto3',1)
INSERT INTO #colores VALUES (1,'rojo')
INSERT INTO #colores VALUES (2,'verde')
SELECT SUM(contador) AS total, color FROM(
SELECT CASE WHEN isnull(t2.id_color,0)=0 THEN 0 ELSE t1.id END AS contador, color --count(*) as total, color
FROM #colores AS t1
LEFT JOIN #autos AS t2 ON (t2.id_color=t1.id)
) AS completa GROUP BY color
Resultado:
3 rojo
0 verde