group by day for the past 5 days

Question

I am trying to select the sum of an integer field for the past 5 days, and I need to group it for each day.

I'm having a bit of issues figuring out the grouping. Here's my sql query so far:

select 
    sum(`amount_sale`) as total 
from `sales` 
where the_date >= unix_timestamp((CURDATE() - INTERVAL 5 DAY))

that works fine for generating the sum for all 5 days together, but I need to break this down so that it shows the sum for each of the past 5 days i.e:

day 1 - $200

day 2- $500

day 3 - $20

etc.

Answer 1

SELECT  DATE(FROM_UNIXTIME(the_date)) AS dt, SUM(amount_sale) AS total
FROM    sales
WHERE   the_date >= UNIX_TIMESTAMP((CURDATE() - INTERVAL 5 DAY))
GROUP BY
        dt

To returns 0 for missing dates:

SELECT  dt, COALESCE(SUM(amount_sale), 0) AS total
FROM    (
        SELECT  CURDATE() - INTERVAL 1 DAY AS dt
        UNION ALL
        SELECT  CURDATE() - INTERVAL 2 DAY AS dt
        UNION ALL
        SELECT  CURDATE() - INTERVAL 3 DAY AS dt
        UNION ALL
        SELECT  CURDATE() - INTERVAL 4 DAY AS dt
        UNION ALL
        SELECT  CURDATE() - INTERVAL 5 DAY AS dt
        ) d
LEFT JOIN
        sales
ON      the_date >= UNIX_TIMESTAMP(dt)
        AND the_date < UNIX_TIMESTAMP(dt + INTERVAL 1 DAY)
GROUP BY
        dt

This is not a very elegant solution, however, MySQL lacks a way to generate recordsets from scratch.

Answer 2

use the format function to return weekday nr: SELECT DATE_FORMAT(the_date, '%w');

Answer 3

use between

like select * from XXX where date between date(...) and date(...) group by date Limit 0,5 should do it