CODEWITHSUNDEEP
mysql
Chud37
Chud37

Reputation: 5007

SQL statement is not giving the last three days

I have the following SQL:

SELECT 
    DATE(`date`), SUM(`total`)
FROM
    `order`
WHERE
    `status` = '3'
        AND `date` > DATE(CURDATE() - INTERVAL 30 DAY)
GROUP BY DAY(`date`)
ORDER BY `date` DESC;

Today is 2014-03-27. But when I var_dump results from this query, I get the following (edited down to save space, numbers edited out)

array(28) {
  [0]=>
  array(2) {
    ["DATE(`date`)"]=>
    string(10) "2014-03-24"
    ["SUM(`total`)"]=>
    string(17) "xxx"
  }
  [1]=>
  array(2) {
    ["DATE(`date`)"]=>
    string(10) "2014-03-23"
    ["SUM(`total`)"]=>
    string(6) "xxx"

..And it finished on 2014-02-25. There are definitely records from the last three days, i can see them in the table. When I run SELECT CURDATE(); I get 2014-03-27. Why is not showing me the last three days?

Upvotes: 1

Views: 76

Answers (2)

entraze
entraze

Reputation: 1

Just use the below Code ..It will display last three days record

SELECT 
    DATE(`date`), SUM(`total`)
FROM
    `order`
WHERE
    `status` = '3'
        AND **DATEDIFF**(DAY, date,GETDATE()) <= 3

ORDER BY `date` DESC;

DATEDIFF Function will return the difference between two days based on the datepart. Here we use the DAY as datepart

Upvotes: 0

xdazz
xdazz

Reputation: 160833

You are doing 

GROUP BY DAY(`date`)

while it should be 

GROUP BY DATE(`date`)

DAY() returns the day of the month for date, from 1 to 31, so there could be same value for different months.

Upvotes: 3

Related Questions