MySql Single Table, Select last 7 days and include empty rows

Question

I have searched similar problems here on stackoverflow but I could not understand how to make this work, what I'm trying to do...

So, I want to get last 7 days transactions from database and get total sales amount and also include empty rows if there is no data for some day.

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

This outputs:

| 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 appreciated :)

possible duplicate of Mysql: Select all data between two dates — Marcus Adams
– Marcus Adams, Commented Apr 25, 2014 at 18:44

Community · Accepted Answer · 2017-05-23 11:45:22Z

16

This is not easy. I took help from this thread generate days from date range and combined it with your query.

So the idea was to get the list of dates from last 7 days then left join these dates with a static amount 0 to the query you have and then finally sum them. This could be used for any date range, just need to change them in both the 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 (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
  ) 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

edited May 23, 2017 at 11:45

CommunityBot

11 silver badge

answered Apr 25, 2014 at 19:16

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Ruddell Over a year ago

this should be the accepted answer... very nice. its dynamic too so you don't have to hardcode dates in.

Gordon Linoff · Accepted Answer · 2014-04-25 19:15:36Z

4

Simply put together a 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;

edited Apr 25, 2014 at 19:15

answered Apr 25, 2014 at 18:39

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

plexcell Over a year ago

Thanks, but its giving some error sqlfiddle.com/#!2/f4eda/41 Exception -> string(282) "SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'union all select date('2014-04-23' union all select date' at line 3"

oguzhankurnuc · Accepted Answer · 2015-12-27 22:33:57Z

0

This is for 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;

answered Dec 27, 2015 at 22:33

oguzhankurnuc

1

Comments

Ebo · Accepted Answer · 2020-07-19 22:35:13Z

0

with recursive all_dates(dt) as (
    select '2014-04-19' as dt
    union all 
    select dt + interval 1 day
    from all_dates
    where dt + interval 1 day <= '2014-04-25'
)
select d.dt as purchase_date, coalesce(m.amount, 0) as purchased
from all_dates as d
left join mytable m
on d.dt = m.purchase_date
order by purchase_date desc;

edited Jul 19, 2020 at 22:35

answered Jul 19, 2020 at 22:22

Ebo

11 bronze badge

1 Comment

Hoppeduppeanut Over a year ago

While this might answer the question, you should edit your answer to include a short explanation of how this script answers the question. This helps to give context and makes the answer much more useful to those who might come across the same issue later on.

Collectives™ on Stack Overflow

MySql Single Table, Select last 7 days and include empty rows

4 Answers 4

1 Comment

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related