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

by

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

More Related Contents:

  1. i have an error which i dont understand. can you help me? [closed]
  2. MySQL pivot row into dynamic number of columns
  3. Rank function in MySQL
  4. MySQL select 10 random rows from 600K rows fast
  5. Count the number of occurrences of a string in a VARCHAR field?
  6. Find nearest latitude/longitude with an SQL query
  7. Simulate lag function in MySQL
  8. Most efficient way to get table row count
  9. How do I check if a column is empty or null in MySQL?
  10. Subqueries with EXISTS vs IN – MySQL
  11. What is this operator in MySQL?
  12. 60 million entries, select entries from a certain month. How to optimize database?
  13. Get most recent row for given ID
  14. Is it possible to GROUP BY multiple columns using MySQL?
  15. MySQL monthly Sale of last 12 months including months with no Sale
  16. Is merge statement available in MySQL
  17. MySQL #1093 – You can’t specify target table ‘giveaways’ for update in FROM clause
  18. Mysql covering vs composite vs column index
  19. MYSQL syntax not evaluating not equal to in presence of NULL
  20. MySQL: LAST_INSERT_ID() returns 0
  21. Calculating a Moving Average MySQL?
  22. MySQL INNER JOIN select only one row from second table
  23. Storing IPv6 Addresses in MySQL
  24. Insert/ Update random date in MySQL
  25. What is the difference between SQL and MySQL? [closed]
  26. how to use a like with a join in sql?
  27. Get total hours worked in a day mysql
  28. MySQL OPTIMIZE all tables?
  29. Strip last two characters of a column in MySQL
  30. Deleting a row based on the max value

Leave a Comment