MySql Single Table, select the last 7 days and include blank rows

Question

I was looking for similar problems here in stackoverflow, but I couldn't figure out how to do this, what I'm trying to do ...

So, I want to get the last 7 days of transactions from the database and get the total amount of sales, and also include blank lines if there is no data on any day.

What I still have: http://sqlfiddle.com/#!2/f4eda/6

It is output:

| PURCHASE_DATE | AMOUNT | |---------------|--------| | 2014-04-25 | 19 | | 2014-04-24 | 38 | | 2014-04-22 | 19 | | 2014-04-19 | 19 | 

What I want:

 | PURCHASE_DATE | AMOUNT | |---------------|--------| | 2014-04-25 | 19 | | 2014-04-24 | 38 | | 2014-04-23 | 0 | | 2014-04-22 | 19 | | 2014-04-21 | 0 | | 2014-04-20 | 0 | | 2014-04-19 | 19 | 

Any help is appreciated :)

Answer 1

Just compile the subquery with the dates you want and use left outer join :

 select d.thedate, coalesce(SUM(amount), 0) AS amount from (select date('2014-04-25') as thedate union all select date('2014-04-24') union all select date('2014-04-23') union all select date('2014-04-22') union all select date('2014-04-21') union all select date('2014-04-20') union all select date('2014-04-19') ) d left outer join transactions t on t.purchase_date = d.thedate and vendor_id = 0 GROUP BY d.thedate ORDER BY d.thedate DESC; 

Answer 2

It is not simple. I got help from this topic to generate days from a date range and combined it with your request.

So, the idea was to get a list of dates for the last 7 days, and then join these dates with a static sum of 0 to your query, and then finally summarize them. This can be used for any date range, just need to change them in both queries

 select t1.purchase_date, coalesce(SUM(t1.amount+t2.amount), 0) AS amount from ( select DATE_FORMAT(a.Date,'%Y-%m-%d') as purchase_date, '0' as amount from ( select curdate() - INTERVAL (aa + (10 * ba) + (100 * ca)) 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 ) a where a.Date BETWEEN NOW() - INTERVAL 7 DAY AND NOW() )t1 left join ( SELECT DATE_FORMAT(purchase_date, '%Y-%m-%d') as purchase_date, coalesce(SUM(amount), 0) AS amount FROM transactions WHERE purchase_date BETWEEN NOW() - INTERVAL 7 DAY AND NOW() AND vendor_id = 0 GROUP BY purchase_date )t2 on t2.purchase_date = t1.purchase_date group by t1.purchase_date order by t1.purchase_date desc 

Demo

Answer 3

This is the last 7 days;

 select d.thedate, coalesce(SUM(amount), 0) AS amount from (select DATE(NOW()) as thedate union all select DATE(DATE_SUB( NOW(), INTERVAL 1 DAY)) union all select DATE(DATE_SUB( NOW(), INTERVAL 2 DAY)) union all select DATE(DATE_SUB( NOW(), INTERVAL 3 DAY)) union all select DATE(DATE_SUB( NOW(), INTERVAL 4 DAY)) union all select DATE(DATE_SUB( NOW(), INTERVAL 5 DAY)) union all select DATE(DATE_SUB( NOW(), INTERVAL 6 DAY))) d left outer join transactions t on t.purchase_date = d.thedate and vendor_id = 0 GROUP BY d.thedate ORDER BY d.thedate DESC;