group by day for the past 5 days
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开发者_Go百科 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.
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.
use the format function to return weekday nr: SELECT DATE_FORMAT(the_date, '%w');
use between
like select * from XXX where date between date(...) and date(...) group by date Limit 0,5 should do it
精彩评论