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
+3
source to share
4 answers
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:
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 |
+2
source to share
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
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
0
source to share
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`)
0
source to share