MySQL LEFT JOIN SUM doen not include 0

Question

Guess there are many varianat of this question, however this has a twist.

My primary table contains logged kilometers for certain dates for certain users:

Table km_run:

|entry|mnumber|dato |km | where 'dato' is the specific date. Formats are like:

|1 |3 |2013-01-01|5.7|

For a specific user ('mnumber') I want to calculate the sum in each week of a year. For this purpose I have made a 'dummy-table' just containing the week numbers from 1 to 53:

Table `week_list`:
|week|
|1   |
|2   |

etc..

This query gives the sum, however I cannot find a way to return a zero if there are no entries in 'km_run' for the specific week.

SELECT `week_list`.`week`, WEEKOFYEAR(`km_run`.`dato`),  SUM(`km_run`.`km`)
FROM `week_list` LEFT JOIN `km_run` ON  WEEKOFYEAR(`dato`) = `week_list`.`week`
WHERE `km_run`.`mnumber` = 3 AND `km_run`.`dato` >= '2013-01-01'
 AND `km_run`.`dato` < '2014-01-01'
GROUP BY WEEKOFYEAR(`dato`)

I have tried to do COALESCE( SUM(km),0) and I have also tried to use the IFNULL function around the sum. Despite the left join, not all records from week_list are returned in the sql statement.

Here's the result:

week | WEEKOFYEAR(`km_run`.`dato`) | SUM(`km_run`.`km`)
1    | 1                           | 58.4
3    | 3                           | 50.7
4    | 4                           | 39.2

As you can see, week two is skipped instead of returning a 0

Answer 1

Firstly JOIN works, creating such rows:
week=2 weekofyear=null mnumber=null sum=0 ...
Then, WHERE clause (for example, where mnumber=3) excludes rows with nulls. You could try something like this:

SELECT week, SUM(km) FROM (
    (SELECT km_run.km AS km, WEEKOFYEAR(km_run.dato) AS week
    FROM km_run
    WHERE mnumber = 3 AND km_run.dato >= '2013-01-01' AND km_run.dato < '2014-01-01')
    UNION
    (SELECT 0 AS km, week_list.week as week FROM week_list)
) GROUP BY week