CODEWITHSUNDEEP
mysqlsql
Shijil
Shijil

Reputation: 9

Collect last 7 days data from SQL and group by days

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.

Upvotes: 0

Views: 2193

Answers (1)

DevSavata
DevSavata

Reputation: 41

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;

Upvotes: 0

Related Questions