mysql - Collect last 7 days data from SQL and group by days

Question

how can I count last 7 days data from SQL and group them by day/date (excluding today). I should be able to use the result as $resultday1, $resultday2, $resultday3 etc. If there was 10 total SQL entries in day 1 (yesterday) $resultday1 should show "10". and the days should be last 7 only, and today/current day should not consider. The following PHP SQL script shows the total count only

SELECT COUNT(1) FROM orders WHERE username='jondoe'

database is a list of referrals made by a registered user in previous days. a single table contains all user's referral details, table name "orders" as per above example.

question from:https://stackoverflow.com/questions/66057531/collect-last-7-days-data-from-sql-and-group-by-days

Answer 1

This is the exact query as you want

SELECT 
    COUNT(*), DATE(order_date) order_date
FROM
    orders
WHERE
    order_date < CURDATE()
        AND order_date > DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY order_date
ORDER BY order_date DESC;