How to display "0" result in SQLite COUNT aggregate?

I have the following tables:

• UserCar (id, user_id, car_id),
• User (id, name)
• Car (id, name)

I’d like to know how many cars the given user has, the query is:

SELECT 
    us.name,
    COUNT(ca.name) as number_of_cars
FROM 
    User us
LEFT JOIN 
    UserCar uc ON us.id = uc.user_id
LEFT JOIN 
    Car ca ON ca.id = uc.car_id
GROUP BY 
    us.id;

The result is fine:

But if I’d like to know how many for example ‘Nissans’ they have, I’d like also to get information about zero results. A little modified query:

SELECT 
    us.name,
    COUNT(ca.name) as number_of_cars
FROM 
    User us
LEFT JOIN 
    UserCar uc ON us.id = uc.user_id
LEFT JOIN 
    Car ca ON ca.id = uc.car_id
WHERE 
    ca.name = 'Nissan'
GROUP BY 
    us.id;

The result is:

I’d like to see the ‘0’ number in the table next to the two other users without a "Nissan" car, but I can only see non-zero values.
I thought about COUNT(*) aggregate function, something similar to the following query:

SELECT
    u.name,
    (SELECT COUNT(*) 
     FROM UserCar uc
     INNER JOIN Car c ON c.id = uc.car_id
     WHERE c.name = 'Nissan'
     GROUP BY uc.car_id) AS number_of_cars
FROM 
    User u;

But I get incorrect results:

What should I do?

>Solution :

Your where clause turns your second left join into an inner join. Put the condition is your select clause like this:

SELECT 
    us.name,
    SUM(ca.name = 'Nissan') as number_of_nissans
FROM User us
LEFT JOIN UserCar uc ON us.id = uc.user_id    
LEFT JOIN Car ca ON ca.id = uc.car_id 
GROUP BY us.id, us.name

Not all SQL engines can add up conditions. Generally this works on all engines:

SUM(case when ca.name = 'Nissan' then 1 else 0 end)