MySQL query - convert to left join to show results with zero?

Question

I'm not very good when it comes to using joins - so I have a single table where I'm counting the number of records that meet certain conditions, and returns those counts by week. The problem is, I need the weeks that have a zero count too....I tried to get this to work with a left join, but I'm struggling...any help appreciated: (Stamp is a datetime field)

Query:

SELECT week(stamp), count(*) AS mycount, YEAR(stamp) as theyear
FROM merges
WHERE completed = 1
AND stamp BETWEEN '2017/4/1 00:00:00' AND '2017/6/1 00:00:00' GROUP BY week(stamp)

This returns:

week(stamp) | mycount | theyear

15 | 21 |2017

17 | 10 |2017

18 | 62 |2017

19 | 13 |2017

20 | 76 |2017

21 | 22 |2017

Notice week 16 is missing? I need to have this result included in the above, like:

16 | 0 |2017

I appreciate any help - I know this isn't too difficult, but I'm pulling my hair out trying to understand how to do this while I read other posts....

What you need is to create an in-line numbers table that contains *all* week numbers that you want to display. Then `LEFT JOIN` your table to it to get all week numbers. — Giorgos Betsos, May 31 '17 at 13:26
Giorgos - I get that concept, I just don't know how to write the query which is why I am posting this. — ShorePatrol, May 31 '17 at 13:46
Could you please check once select week('2017/4/1 00:00:00'). This is returning 13. So I assume this should also come. — Fahad Anjum, May 31 '17 at 18:14

Fahad Anjum · Answer 1 · 2017-06-01T14:04:17.327

select weekValue, yearValue, coalesce(mycount,0)
from 

( SELECT distinct week(@startDate := @startDate + Interval 1 day) as weekValue,
year(@startDate := @startDate + Interval 1 day) as yearValue
FROM 
(select 0 union all select 1 union all select 3 union all select 4 
    union all select 5 union all select 6 union all select 6 union all select 7 
    union all select 8 union all select 9) t,
(select 0 union all select 1 union all select 3 
    union all select 4 union all select 5 union all select 6 
    union all select 6 union all select 7 union all select 8 union all select 9) t2,
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t3, 
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t4, 
(SELECT @startDate := '2017-03-31 00:00:00' ) as g

where 
@startDate < '2017-06-01 00:00:00' ) as generateWeekYear left join 

(SELECT week(stamp) as theweek, count(*) AS mycount, YEAR(stamp) as theyear
FROM merges
WHERE completed = 1
AND stamp BETWEEN '2017/4/1 00:00:00' AND '2017/6/1 00:00:00' GROUP BY week(stamp) ) as actualQuery

on  generateWeekYear.weekValue = actualQuery.theweek 
and generateWeekYear.yearValue = actualQuery.theyear

Let me explain the above query,

Sub Query generateWeekYear = This is used to genearate distinct week and year based on two inputs lets say startDate and endDate. startDate should be 1 day less to actual startDate. Because if you do not subtract 1 day then there might chance to loose one week.

Now you have all week and year which needs to be displayed.

Now you are thinking generateWeekYear is going to be more time to execute but this is not case. You can check this generate an integer sequence in MySQL.

After that you simply join your table with above table and you can get your required result.

Wow - this is far more complicated than I thought - but I thank you for the query - it looks like the mycount field shows NULL, instead of 0. I tried using coalesce(count(*), 0) as mycount, but that still shows NULL. Also not sure why you need to subtract a day? Also the end date time should be 11:59:59 PM - this was a mistake in my query. — ShorePatrol, Jun 01 '17 at 11:45
Use coalesce(my count, 0). Now you can manipulate with dates. It will give you correct result. If you think you can mark my answer as correct — Fahad Anjum, Jun 01 '17 at 13:51
If you execute this query then you will see date is starting from 2017-04-02 instead of 2017-04-01 that is it is subtracted 1 day. — Fahad Anjum, Jun 01 '17 at 14:06

MySQL query - convert to left join to show results with zero?

1 Answers1