How to Retrieve Complete Sales Data for the Last Seven Days from a MySQL Table, Including Days with No Transactions?

Retrieving Comprehensive Data from a MySQL Single Table: Covering Empty Rows and Recent Transactions

In this discussion, we aim to address a common challenge faced when querying a database: how to incorporate both recent transactions and empty days into the results. Specifically, we will focus on retrieving the last seven days of sales transactions while including empty rows for days without any activity.

To begin, we will leverage the capabilities of the COALESCE() function, which enables us to substitute a default value if the expression evaluates to NULL. In this case, we will use 0 as the default value for empty rows.

We will then create a subquery to generate a comprehensive list of dates spanning the last seven days. This will serve as the basis for the complete result set, ensuring that empty rows are included.

Finally, we will perform a LEFT JOIN operation between the generated date list and the transactions table, matching records based on the PURCHASE_DATE column. This will merge the transactions with the empty date records, allowing us to calculate the total sales amount for each day and display both recent transactions and empty rows.

The resulting query will effectively provide a comprehensive overview of the last seven days of sales transactions, accounting for days with no recorded activity.

Here is the complete query for reference:

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()
        GROUP BY 
            purchase_date
    ) t2
ON 
    t2.purchase_date = t1.purchase_date
GROUP BY 
    t1.purchase_date
ORDER BY 
    t1.purchase_date DESC;

The above is the detailed content of How to Retrieve Complete Sales Data for the Last Seven Days from a MySQL Table, Including Days with No Transactions?. For more information, please follow other related articles on the PHP Chinese website!