How to Retrieve Last 7 Days' Transactions with Empty Rows in a Single MySQL Table?

Retrieving Last 7 Days' Transactions and Including Empty Rows in MySQL Single Table

To address the challenge of extracting the last 7 days' transactions and capturing empty rows from a MySQL database, we will traverse the following approach:

1. Generate a Date Range:
   We start by creating a list of dates spanning the previous seven days using the following query:

   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()

2. Join with Transaction Table:
   Next, we join this generated date list with the transactions table to obtain the corresponding sales amounts, if any:

   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

Full Query:

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

Utilising this comprehensive query, we can extract the total sales for each of the last seven days, effectively including empty rows for days without transactions.

The above is the detailed content of How to Retrieve Last 7 Days' Transactions with Empty Rows in a Single MySQL Table?. For more information, please follow other related articles on the PHP Chinese website!