Sonamor Sonamor - 2 months ago 7
MySQL Question

MySQL - Return null values if none

I have the following schema in MySQL (read only permissions database)

http://sqlfiddle.com/#!9/1eafc/1

As you can see there are only 5 country codes:

GB, USA, GR, ES, DE


Some months some of the records might not contain one of the countries because no record was created for this country that month.

As you can see in the sqlfiddle,
GB
records were only created in September.

Is there any way if there isn't any record for a country in that month, instead of not being returned, to show 0 or NULL or something?

I've tried so far different variations of
ISNULL
,
IFNULL
and
COALESCE
but none of them worked.

I want to return something like the following

UK 0
USA 13
GR 5
ES 12
DE 1


Any ideas?

Answer

Ok, so in lieu of a functional SQLFiddle I will make a few assumptions. The country code exists in the same table as the grouped data.

If that is the case, why not use:

select DT1.CountryCode, count(DT2.ValueForCounting) as ReturnedCount
from (
select distinct CountryCode
from DataTable ) DT1
left join DataTable DT2
on DT1.CountryCode = DT2.CountryCode
and DT2.QueryConditions = 'Stuff'    -- Use the join condition instead of a where clause for anything to do with DT2