Teddybugs Teddybugs - 1 month ago 5
SQL Question

MYSQL: JOIN Non-Exist data to a table and sum it

i have sql like this:

SELECT
m.MONTH
FROM (
SELECT '1' AS MONTH
UNION ALL SELECT '2' AS MONTH
UNION ALL SELECT '3' AS MONTH
UNION ALL SELECT '4' AS MONTH
UNION ALL SELECT '5' AS MONTH
UNION ALL SELECT '6' AS MONTH
UNION ALL SELECT '7' AS MONTH
UNION ALL SELECT '8' AS MONTH
UNION ALL SELECT '9' AS MONTH
UNION ALL SELECT '10' AS MONTH
UNION ALL SELECT '11' AS MONTH
UNION ALL SELECT '12' AS MONTH
) AS m


which will display month 1 to 12

i have a table like this:
tableA:

YEAR | MONTH | A | B |
2015 | 1 | 1 | 3 |
2016 | 1 | 2 | 2 |
2013 | 1 | 3 | 2 |
2016 | 2 | 4 | 1 |
2016 | 3 | 5 | 1 |
2016 | 4 | 6 | 3 |


Im trying to select sum the A and B all the month from 1-12 even if the month on tableA not exist to display as 0 for year 2016.

Result:

expected result are like below:

MONTH | YEAR | SUM_OF_A | SUM_OF_B |
1 | 2016 | 6 | 3 |
2 | 2016 | 0 | 0 |
3 | 2016 | 5 | 1 |
4 | 2016 | 6 | 3 |
5 | 2016 | 0 | 0 |
6 | 2016 | 0 | 0 |
7 | 2016 | 0 | 0 |
8 | 2016 | 0 | 0 |
9 | 2016 | 0 | 0 |
10 | 2016 | 0 | 0 |
11 | 2016 | 0 | 0 |
12 | 2016 | 0 | 0 |


im trying to join the month SQL with tableA using following SQL, but the result are not correct.

SELECT
m.MONTH, tableA.YEAR,
CASE WHEN tableA.A is not null THEN sum(tableA.A) ELSE 0 END AS SUM_OF_A,
CASE WHEN tableA.B is not null THEN sum(tableA.B) ELSE 0 END AS SUM_OF_B,
FROM (
SELECT '1' AS MONTH
UNION ALL SELECT '2' AS MONTH
UNION ALL SELECT '3' AS MONTH
UNION ALL SELECT '4' AS MONTH
UNION ALL SELECT '5' AS MONTH
UNION ALL SELECT '6' AS MONTH
UNION ALL SELECT '7' AS MONTH
UNION ALL SELECT '8' AS MONTH
UNION ALL SELECT '9' AS MONTH
UNION ALL SELECT '10' AS MONTH
UNION ALL SELECT '11' AS MONTH
UNION ALL SELECT '12' AS MONTH
) AS m
LEFT OUTER JOIN tableA on m.MONTH = tableA.MONTH
where tableA.YEAR=2016
group by tableA.MONTH


any help would be great.

Answer
where tableA.YEAR=2016

When you put a condition on any column tableA, only rows where tableA.YEAR is not NULL will pass the condition. Therefore you're eliminated the non-matching rows from the OUTER JOIN.

Put that condition in your JOIN...ON condition.

Comments