How to deal: my 'count' query does not get zero, when records not exist-CodePudding

I have a situation when it's needed to have a row of customer info (name, address, number etc.) and a count of cars he has. Each person are able to have any count of cars (car_name, VIN etc.)

And so i need to have a view with record info about customer and count of cars he has

SQL query

SELECT customer.id, customer.name, COUNT(car.id), customer.ipn, customer.address
FROM customer
LEFT JOIN car ON customer.id = car.customer_id

Everything OK for customers, that have cars, but for those, who not, bad, because they are not shown. I want to see zero, as well

DATA SET

TABLE 'Customer'

TABLE 'Car'

VIEW RESULT

CodePudding user response：

Use Join with Group By

SELECT cu.id,cu.name,COUNT(cu.id) AS [COUNT],cu.ipn,cu.address,cu.number,cu.email
FROM Customer cu 
JOIN Car ca ON cu.id = ca.customer_id
GROUP BY cu.id,cu.name,cu.ipn,cu.address,cu.number,cu.email

CodePudding user response：

you can try this one !

SELECT customer.id, customer.name,ISNULL(COUNT(car.id) OVER(ORDER BY car.id),0) AS Cnt, customer.ipn, customer.address
FROM customer
LEFT JOIN car ON customer.id = car.customer_id