CODEWITHSUNDEEP
mysql
BigBug
BigBug

Reputation: 6290

get the SUM between two given dates

if i want to get the total_consumption over a range of dates, how would i do that?

I thought i could do:

SELECT id, SUM(consumption) 
FROM consumption_info 
WHERE date_time BETWEEN 2013-09-15 AND 2013-09-16 
GROUP BY id;

however this returns: Empty set, 2 warnings(0.00 sec)

---------------------------------------
id | consumption |  date_time          |        
=======================================|
1  |      5      | 2013-09-15 21:35:03 |
2  |      5      | 2013-09-15 24:35:03 |
3  |      7      | 2013-09-16 11:25:23 |
4  |      3      | 2013-09-16 20:15:23 |
----------------------------------------

any ideas what i'm doing wrong here?

thanks in advance

Upvotes: 4

Views: 11087

Answers (3)

Hana Ziquel
Hana Ziquel

Reputation: 21

SELECT SUM(consumption) 
FROM consumption_info 
WHERE date_time >= 2013-09-15 AND date_time <= 2013-09-16;

or

SELECT SUM(consumption) 
FROM consumption_info 
WHERE date_time BETWEEN 2013-09-15 AND 2013-09-16;

Upvotes: 2

raina77ow
raina77ow

Reputation: 106385

You're missing quotes around the date strings: the WHERE clause should actually be written as...

BETWEEN '2013-09-15' AND '2013-09-16'

The irony is that 2013-09-15 is a valid SQL expression - it means 2013 minus 09 minus 15. Obviously, there's no date lying in between the corresponding results; hence an empty set in return

Yet there might be another, more subtle error here: you probably should have used this clause...

BETWEEN '2013-09-15 00:00:00' AND '2013-09-16 23:59:59'

... instead. Without setting the time explicitly it'll be set to '00:00:00' on both dates (as DATETIME values are compared here).

While it's obviously ok for the starting date, it's not so for the ending one - unless, of course, exclusion of all the records for any time of that day but midnight is actually the desired outcome.

Upvotes: 6

Viji
Viji

Reputation: 2629

Its better to use CAST when comparing the date function.

SELECT id, SUM(consumption) 
FROM consumption_info 
WHERE date_time 
    BETWEEN CAST('2013-09-15' AS DATETIME) 
    AND CAST('2013-09-16' AS DATETIME) 
GROUP BY id;

Upvotes: 1

Related Questions