Receiving a full daily sale in 7 days
I am trying to get the date and SUM (total) for the last 7 days. I try this code and get NULL date and daily_sale 0.00
SELECT
date,
IFNULL( SUM( total ), 0 ) AS daily_sale
FROM sales
WHERE date BETWEEN NOW()
AND DATE_SUB(NOW(), INTERVAL 7 DAY)
I have no doubt how to get the date and daily total sale. I want to get results like
date daily_sale
2013-01-29 500.00
2013-01-28 500.00
2013-01-27 500.00
2013-01-26 500.00
...
2013-01-23 500.00
You can try with interval - 7 day
:
SELECT date, SUM(Coalese(total,0)) AS daily_sale
FROM yourtable
WHERE date BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL -7 DAY)
Don't know why it between
doesn't work, check this:
SQLFIDDLE DEMO
select dates, sum(coalesce(daily_sale,0)) as total
from sales
where dates <= NOW()
and dates >= Date_add(Now(),interval - 7 day)
group by dates
;
| DATES | TOTAL |
------------------------------------------
| January, 23 2013 00:00:00+0000 | 500 |
| January, 24 2013 00:00:00+0000 | 500 |
| January, 27 2013 00:00:00+0000 | 1500 |
| January, 28 2013 00:00:00+0000 | 1000 |
| January, 29 2013 00:00:00+0000 | 500 |
from MySQL BETWEEN ... AND manual
expr BETWEEN min AND max
SO change it to
SELECT CURDATE( ),DATE_SUB( CURDATE() , INTERVAL 7 DAY ) AS weekEndDate,
`date` , SUM( COALESCE(`daily_sale`,0 ) ) AS weekly_sale
FROM tbl_sale
GROUP BY weekEndDate
LIMIT 0 , 30
Working DEMO
Note:
- date is the mysql backup keyword, so wrap it in
`
- the SUM syntax is
SUM(column_name)
and your column name is daily_sales
If the first date expression needs to be bottom as @ diEcho's answer suggests, then you need to make the NOW()
second term in the expression, not the first.
You must use the lowest date first in the BETWEEN command, so NOW () must be at the end
SELECT
date,
IFNULL( SUM( total ), 0 ) AS daily_sale
FROM sales
WHERE date BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) and NOW()
or if you need statistics for each day:
SELECT
DATE(`date`),
IFNULL( SUM( total ), 0 ) AS daily_sale
FROM sales
WHERE date BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) and NOW()
group by DATE(`date`)