How to Include Empty Rows in a MySQL Query Showing Daily Transactions for the Last 7 Days?

Integrating Empty Rows into a MySQL Single Table Query for the Previous 7 Days

In this query, we aim to retrieve all transactions from the last seven days, ensuring that even days with no recorded sales are included in the results. We utilize a technique that combines two separate queries to achieve this outcome.

Firstly, we enumerate all dates within the past seven days using a nested SELECT statement. This query effectively generates a list of dates that serves as a foundation for our transaction data.

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

Next, we combine this comprehensive list of dates with a query that aggregates daily sales data from our transactions table. This join ensures that we include both recorded transactions and empty rows for dates with no activity.

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

By seamlessly integrating these two queries, we effectively construct a single table that chronologically lists all dates within the specified range, with corresponding sales data or zeroes where no transactions occurred.

The above is the detailed content of How to Include Empty Rows in a MySQL Query Showing Daily Transactions for the Last 7 Days?. For more information, please follow other related articles on the PHP Chinese website!