SQL Show Null Value Group By

Question

This is table in my database

+--+-------+---------+----------+--------+
|ID|OrderID|ProductID|OrderDate |Quantity|
| 1|1      |1        |2017-01-01|1       |
| 2|1      |1        |2017-01-01|6       |
| 3|1      |1        |2017-01-03|9       |
| 4|1      |1        |2017-01-04|3       |
| 5|1      |1        |2017-01-05|5       |
| 6|1      |1        |2017-01-07|1       |
| 7|1      |1        |2017-01-09|2       | 
+--+-------+---------+----------+--------+

I want to show the data like this

+----------+----+
|2017-01-01|7   |
|2017-01-02|0   |
|2017-01-03|9   |
|2017-01-04|3   |
|2017-01-05|5   |
|2017-01-06|0   |
|2017-01-07|1   |
|2017-01-08|0   | 
|2017-01-09|2   |
+----------+----+

What query I should use to make it possible, I've tried to using group by OrderDate but when there is null it doesnt show

you would need a dates table to do this. do you have such a table in your database? — Vamsi Prabhala, May 14 '17 at 16:23
I mean when there is no quantity in that date, I'm using mysql — madiluzi, May 14 '17 at 16:29

score 0 · Answer 1 · edited May 23 '17 at 12:26

One way to generate date between a given date range is give in this answer.

You either generate the rows on the fly or better yet, create a calendar table and use it for purposes such as this.

Here is a solution based on the above linked solution:

select d.day, sum(t.quantity) as quantity
from (
    select min_orderdate + INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (
        select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9
        ) as a
    cross join (
        select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9
        ) as b
    cross join (
        select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9
        ) as c
    join (
        select min(orderdate) min_orderdate, max(orderdate) max_orderdate from your_table
        ) t on min_orderdate + INTERVAL (a.a + (10 * b.a) + (100 * c.a)) <= max_orderdate
    ) d
left join your_table t on d.day = t.orderdate
group by d.day;

Here is a Demo of the same.

SQL Show Null Value Group By

1 Answers1