How to Retrieve Last 7 Days' Transaction Data with Zero Sales Rows in MySQL?

MySql Single Table: Selecting Last 7 Days with Empty Rows

Problem:

The objective is to retrieve transaction data from the last 7 days and display the total sales amount for each day, including empty rows for days with no transactions. The following query returns only days with non-zero sales:

SELECT PURCHASE_DATE, SUM(AMOUNT) AS AMOUNT
FROM transactions
WHERE PURCHASE_DATE BETWEEN NOW() - INTERVAL 7 DAY AND NOW()
GROUP BY PURCHASE_DATE;

Solution:

To achieve the desired result, a combination of queries is utilized:

1. Generate a List of Dates:

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

This query generates a list of dates from the last 7 days.

2. Join with Transaction Data:

   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;

This query left joins the generated date list with the actual transaction data, adding empty rows for days with no transactions. Coalescing is used to handle null values.

The above is the detailed content of How to Retrieve Last 7 Days' Transaction Data with Zero Sales Rows in MySQL?. For more information, please follow other related articles on the PHP Chinese website!