Query with CASE statement in Group By I need to create an entry where no results occur in the WHEN

Question

I have a query which groups up incoming payments into date ranges (1-7 days, 3-6 months etc.) and it largely works as I had hoped. However, I want to return a row which says 0 when no income is expected in the date range.

The group by looks like this:

group by 

CASE    WHEN timestampdiff(day,curdate(),data.duedate) between 0 and 7 then 1
        WHEN timestampdiff(day,curdate(),data.duedate) between 8 and 14 then 2
        WHEN timestampdiff(day,curdate(),data.duedate) between 15 and 30 then 3
        WHEN timestampdiff(month,curdate(),data.duedate) between 1 and 2 then 4
        WHEN timestampdiff(month,curdate(),data.duedate) between 2 and 3 then 5
        WHEN timestampdiff(month,curdate(),data.duedate) between 3 and 6 then 6
        WHEN timestampdiff(month,curdate(),data.duedate) between 6 and 12 then 7
        WHEN timestampdiff(year,curdate(),data.duedate) between 1 and 2 then 8
        WHEN timestampdiff(year,curdate(),data.duedate) between 2 and 3 then 9
        WHEN timestampdiff(year,curdate(),data.duedate) between 3 and 4 then 10
        WHEN timestampdiff(year,curdate(),data.duedate) between 5 and 6 then 11
        WHEN timestampdiff(year,curdate(),data.duedate) >= 7 then 12

This works correctly in that it will give me the correct amounts, but I want to force the code to give me a 0. So I currently get this:

1   300000
5   150000
8   300000

What I actually want is this:

1   300000
2   0
3   0
4   0
5   150000
6   0
7   0
8   300000
etc.

This is the entire query - I've tried using an IFNULL() but had no success:

select

sum(data.principaldue+data.interestdue) as m

from
(select
    la.id
    ,rep.duedate
    ,rep.PRINCIPALDUE
    ,rep.INTERESTDUE
    from repayment rep
    join loanaccount la on la.ENCODEDKEY = rep.PARENTACCOUNTKEY
    join loanproduct lp on lp.ENCODEDKEY = la.PRODUCTTYPEKEY

group by 

CASE    WHEN timestampdiff(day,curdate(),data.duedate) between 0 and 7 then 1
        WHEN timestampdiff(day,curdate(),data.duedate) between 8 and 14 then 2
        WHEN timestampdiff(day,curdate(),data.duedate) between 15 and 30 then 3
        WHEN timestampdiff(month,curdate(),data.duedate) between 1 and 2 then 4
        WHEN timestampdiff(month,curdate(),data.duedate) between 2 and 3 then 5
        WHEN timestampdiff(month,curdate(),data.duedate) between 3 and 6 then 6
        WHEN timestampdiff(month,curdate(),data.duedate) between 6 and 12 then 7
        WHEN timestampdiff(year,curdate(),data.duedate) between 1 and 2 then 8
        WHEN timestampdiff(year,curdate(),data.duedate) between 2 and 3 then 9
        WHEN timestampdiff(year,curdate(),data.duedate) between 3 and 4 then 10
        WHEN timestampdiff(year,curdate(),data.duedate) between 5 and 6 then 11
        WHEN timestampdiff(year,curdate(),data.duedate) >= 7 then 12

 END 

Order by

CASE    WHEN timestampdiff(day,curdate(),data.duedate) between 0 and 7 then 1
        WHEN timestampdiff(day,curdate(),data.duedate) between 8 and 14 then 2
        WHEN timestampdiff(day,curdate(),data.duedate) between 15 and 30 then 3
        WHEN timestampdiff(month,curdate(),data.duedate) between 1 and 2 then 4
        WHEN timestampdiff(month,curdate(),data.duedate) between 2 and 3 then 5
        WHEN timestampdiff(month,curdate(),data.duedate) between 3 and 6 then 6
        WHEN timestampdiff(month,curdate(),data.duedate) between 6 and 12 then 7
        WHEN timestampdiff(year,curdate(),data.duedate) between 1 and 2 then 8
        WHEN timestampdiff(year,curdate(),data.duedate) between 2 and 3 then 9
        WHEN timestampdiff(year,curdate(),data.duedate) between 3 and 4 then 10
        WHEN timestampdiff(year,curdate(),data.duedate) between 5 and 6 then 11
        WHEN timestampdiff(year,curdate(),data.duedate) >= 7 then 12

 END

Answer 1

This is not a complete answer, but would be too big for comments;

A temporary table with numbers could be useful:

MySql temporary tables:

CREATE TEMPORARY TABLE TempTable (num int); 
INSERT INTO TmpTable VALUES(1,2,3,4,5,6,7,8,9,10,11,12 ...); 

Then you could right join on this table to make sure missing values are included.

Lets say you have this:

results(num, val):
1   300000
5   150000
8   300000

This should result in your desired output:

SELECT numbers.num, COALESCE(results.val, 0) as val
FROM results RIGHT JOIN TempTable numbers on results.num = numbers.num
WHERE numbers.num <= 12 --or other max number

1   300000
2   0
...
5   150000
...

Hope this helps.

Edit:

If you don't have permission to create temporary tables, look for a workaround to select consecutive integers, for example:

SELECT @row := @row + 1 as row, t.*
FROM some_table t, (SELECT @row := 0) r

Where some_table is any table with enough rows.

Probably use a top N on that.

Another dirty workaround, might be good enough if you don't need many numbers:

SELECT 1 num
UNION
SELECT 2 num
UNION
...

Edit:

Slightly tidier workaround:

SELECT * FROM (VALUES (1), (2), (3), ... ) x(i)