Hi

Im trying to make a query which counts the number of venues in each county of the uk from 2 tables of my database. The trouble im having is in making counties with 0 venues show up in the results, it seems that the group function removes all trace of counties which have no venues.

it goes like this -

select p.county, count(u.venueid) from postcodes p
left join venue u on SUBSTRING_INDEX(u.postcode, ' ', 1)=p.postcode
where u.status='active'
group by p.county order by p.county


It must be a common issue but i can only find 1 other post about it and it has no replies.

anybody have any ideas?

P is the outer table in your join, so p.country does not exist. To get a value out of it, you'll need to coalesce or isnull it.