MySQL - Return null values if none

Question

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

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?

You probably want to `SELECT ... FROM country_codes LEFT JOIN records ON ... WHERE ...`, this will include even the countries, that have 0 records. Just guessing, since Your SQL fiddle won't load :-( — Roman Hocke, Sep 29 '16 at 09:15
See e.g. [MySQL how to fill missing dates in range?](http://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range), [Mysql - filling rows for missing months](http://stackoverflow.com/questions/25358457/mysql-filling-rows-for-missing-months) — Solarflare, Sep 29 '16 at 09:19

JohnHC · Accepted Answer · 2016-09-29T10:02:51.977

2

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

edited Sep 29 '16 at 10:02

answered Sep 29 '16 at 09:17

JohnHC

10,935
1
24
40

I tried it, but If I add a where clause to limit the results just for the previous month, it still shows up less countries – Sonamor Sep 29 '16 at 10:00
@Sonamor You don't use a `Where` with this method, you apply it to the join condition – JohnHC Sep 29 '16 at 10:02
I wasn't aware that you are able to add conditions in a Join statement – Sonamor Sep 29 '16 at 10:13

score 0 · Answer 2 · answered Sep 29 '16 at 09:13

0

your fiddle link not loading,

but i guess my two different ideas about that,

create function and manage it using if condition. or
create view among month, country and mapping table and put month on left join

hope it works.

answered Sep 29 '16 at 09:13

PSabuwala

155
1
9

MySQL - Return null values if none

2 Answers2