CODEWITHSUNDEEP
mysqlsql
timmyc
timmyc

Reputation: 522

MySQL select overlapping count

I would like to combine these four queries into one:

SELECT COUNT(ID) FROM conversations WHERE DAY(create_time)=DAY(NOW());
SELECT COUNT(ID) FROM conversations WHERE WEEKOFYEAR(create_time)=WEEKOFYEAR(NOW());
SELECT COUNT(ID) FROM conversations WHERE MONTH(create_time)=MONTH(NOW());
SELECT COUNT(ID) FROM conversations WHERE YEAR(create_time)=YEAR(NOW());

This single query would return to me, for example

day => # of conversations from today,

week => # of conversations from this week,

month => # of conversations from this month,

year => # of conversations from this year

I thought I was about there with some CASE syntax (I'm no MySQL expert), but realized each record seemed to only be able to be counted in one case, and I need them to overlap. In other words, I could have a single record counted for one of these cases, two, three, or all four.

Upvotes: 2

Views: 459

Answers (2)

mellamokb
mellamokb

Reputation: 56779

select
    sum(case when DAY(create_time)=DAY(NOW()) then 1 else 0 end) as DayCount,
    sum(case when WEEKOFYEAR(create_time)=WEEKOFYEAR(NOW()) then 1 else 0 end) as WeekCount,
    sum(case when MONTH(create_time)=MONTH(NOW()) then 1 else 0 end) as MonthCount,
    sum(case when YEAR(create_time)=YEAR(NOW()) then 1 else 0 end) as YearCount
from
    conversations
where
    YEAR(create_time)=YEAR(NOW())

Demo: http://www.sqlfiddle.com/#!2/e62f0/1

Upvotes: 4

mechanical_meat
mechanical_meat

Reputation: 169424

Have a look at the documentation for UNION.
Example usage:

SELECT COUNT(ID) FROM conversations WHERE DAY(create_time)=DAY(NOW());
UNION ALL
SELECT COUNT(ID) FROM conversations WHERE WEEKOFYEAR(create_time)=WEEKOFYEAR(NOW());
UNION ALL 
SELECT COUNT(ID) FROM conversations WHERE MONTH(create_time)=MONTH(NOW());
UNION ALL
SELECT COUNT(ID) FROM conversations WHERE YEAR(create_time)=YEAR(NOW());

Upvotes: 1

Related Questions