How to count at specific date range in mysql

Question

I have created this mysql code to count at only specific date range, i dumped the output of my mysql code and here it is:

SELECT COUNT(
                IF(
                        DATE(q.date_emailed) BETWEEN '2014-02-01' AND '2014-02-28',
                        1,
                        0
                    )
        ) AS 'Feb', 
        COUNT(
                    IF(
                        DATE(q.date_emailed) BETWEEN '2014-03-01' AND '2014-03-31',
                        1,
                        0
                    )
         ) AS 'March', 
         COUNT(
                    IF(
                        DATE(q.date_emailed) BETWEEN '2014-01-01' AND '2014-01-31',
                        1,
                        0
                    )
         ) AS 'Jan' 
        FROM
            database.quotes q
        WHERE (DATE(q.date_emailed) BETWEEN '2014-01-01' AND '2014-03-31')

But this outputs same count for each month, in which i confirmed that february and march has zero counts. What am I missing here?

What is the data type of date_emailed?

Gordon Linoff
– Gordon Linoff

2014-03-13 20:31:13 +00:00
Commented Mar 13, 2014 at 20:31 — Gordon Linoff
– Gordon Linoff, Commented Mar 13, 2014 at 20:31
@Gordon Linoff: the datatype is datetime

user2881063
– user2881063

2014-03-13 20:34:11 +00:00
Commented Mar 13, 2014 at 20:34 — user2881063
– user2881063, Commented Mar 13, 2014 at 20:34

Gordon Linoff · Accepted Answer · 2014-03-13 20:45:22Z

6

COUNT() counts non-null values. You can fix your code by using sum() instead. You can also simplify it by removing the if statements:

SELECT SUM(DATE(q.date_emailed) BETWEEN '2014-02-01' AND '2014-02-28') AS Feb, 
       SUM(DATE(q.date_emailed) BETWEEN '2014-03-01' AND '2014-03-31') AS March, 
       SUM(DATE(q.date_emailed) BETWEEN '2014-01-01' AND '2014-01-31') AS Jan
FROM database.quotes q
WHERE DATE(q.date_emailed) BETWEEN '2014-01-01' AND '2014-03-31';

In MySQL, a boolean result is treated as 0 for "false" and 1 for "true". This is a great convenience and allows you to use sum() to count the number of matches.

Note that I also removed the single quotes around the column names. Single quotes should be used for string and date constants, not for identifiers.

EDIT:

You can have this query run faster by using an index on q.date_emailed. However, I don't think the index will be used because of the date() function. You can fix this by changing the logic:

SELECT SUM(DATE(q.date_emailed) BETWEEN '2014-02-01' AND '2014-02-28') AS Feb, 
       SUM(DATE(q.date_emailed) BETWEEN '2014-03-01' AND '2014-03-31') AS March, 
       SUM(DATE(q.date_emailed) BETWEEN '2014-01-01' AND '2014-01-31') AS Jan
FROM database.quotes q
WHERE q.date_emailed >= '2014-01-01' AND
      q.date_emailed < '2014-04-01';

edited Mar 13, 2014 at 20:45

answered Mar 13, 2014 at 20:30

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2881063 Over a year ago

I dont want to add any fields, i just want to count the number of records in the quotes table at that specific date ranges.

user2881063 Over a year ago

I didn't know what your SUM does but it worked. LOL Thanks a lot sir.

Niels · Accepted Answer · 2014-03-13 20:29:35Z

2

You should use SUM instead of COUNT if you want to SUM up the 0 and 1.

The COUNT will COUNT the number of row, the SUM will sum up the values.

answered Mar 13, 2014 at 20:29

Niels

50.1k4 gold badges68 silver badges86 bronze badges

3 Comments

user2881063 Over a year ago

They are mysql's if statements, I think 1 is for true and 0 is for false, am i right? why would i want to add them?

Niels Over a year ago

COUNT will count the number of rows, SUM will sum up the 1 and 0. Which is your required output?

user2881063 Over a year ago

I dont want to add any fields, i just want to count the number of records in the quotes table at that specific date ranges.

Collectives™ on Stack Overflow

How to count at specific date range in mysql

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related