CODEWITHSUNDEEP
mysql
Goku__
Goku__

Reputation: 970

How to group by month by providing a start date and an end date?

I am using the following query.

select DATE_FORMAT(created_at, '%Y-%m') AS month, sum(points) from table_name group by month;

I am getting the sum of points but the result is not consistent with the result I get when I try by a date range . Eg,

select sum(points) from table_name where '2015-01-01 00:00:00' <= date(created_at) <= '2015-01-31 23:59:59';

The result of Jan, 2015 by 1st and 2nd query above are not same. Where is the problem in the first query?

Upvotes: 0

Views: 332

Answers (1)

Barranka
Barranka

Reputation: 21047

The way you're writing your second query is not valid. You can only use binary expressions, so your query should be like this:

select sum(points) 
from table_name 
where '2015-01-01 00:00:00' <= date(created_at)
  and date(created_at) <= '2015-01-31 23:59:59';

In your original form, the where is being evaluated like this (I think):

  • First: '2015-01-01 00:00:00' <= date(created_at)

    Assuming this is true, this would be evaluated as 1

  • Second: '2015-01-01 00:00:00' <= date(created_at) <= '2015-01-31 23:59:59',

    but this is equivalent to 1 <= '2015-01-31 23:59:59', so... it's of course not what you want

Upvotes: 1

Related Questions