Get count on ALL rows even if 0

Hi Guys!

I’m trying to get a count on ALL rows using this query, but if the count is equal to 0 at the moment it just inserts NULL on the count column. Any idea how to get a value of 0 instead?


SELECT locations . * , l.count
FROM locations
LEFT JOIN (

SELECT count( job_id ) AS count, display_county
FROM jobs_locations
GROUP BY display_town
) AS l ON l.display_county = locations.location_name
GROUP BY locations.id
ORDER BY locations.location_name

change this from your outer select:
l.count
to this
COALESCE(l.count,0)

Thanks for your post, but it didn’t seem to work for me…

why are you grouping by town, but joining on county???

i have a feeling you need a refresher on grouping