CODEWITHSUNDEEP
mysqlsql
Saleem
Saleem

Reputation: 1049

Getting Total Daily Sale for 7 days

I am trying to get the date and SUM(total) for last 7 days. I am trying this code and getting date NULL 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 am note sure how to get the date and daily total sale. I want get results as

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

Upvotes: 3

Views: 2721

Answers (4)

xkeshav
xkeshav

Reputation: 54032

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 mysql reserve Keyword so wrap it in `
  • syntax of SUM is SUM(column_name) and your column name is daily_sales

Upvotes: 0

bonCodigo
bonCodigo

Reputation: 14361

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)

Not sure why between didn't work, check this out:

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 |

Upvotes: 2

valex
valex

Reputation: 24144

You should use the lowest date first in BETWEEN command so NOW() have to be in 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 statistic 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`)

Upvotes: 0

cms_mgr
cms_mgr

Reputation: 2017

If the first date expression needs to be the lower one, as @diEcho's answer suggests, then you need to make NOW() the second term in the expression, not the first.

Upvotes: 0

Related Questions