Hi guys

Lets say I have a table:
Stats (id, person_id, creation_date)

I want to select stats for person_id = 1. I need to retrieve a
count(*) for person_id = 1, but for different times.

1 - How many times the person_id is in the table during the last
month?
2 - How many times the person_id is in the table during the last 3
months?
3 - How many times the person_id is in the table during the last 12
month?

I can of course make 3 different queries:

select count(*) as total_1_month from stats where person_id = 1 and
creation_date > DATE_SUB(curdate(), INTERVAL 1 MONTH);
select count(*) as total_3_month from stats where person_id = 1 and
creation_date > DATE_SUB(curdate(), INTERVAL 3 MONTH);
select count(*) as total_12_month from stats where person_id = 1 and
creation_date > DATE_SUB(curdate(), INTERVAL 12 MONTH);

Is it possibile to select these 3 kinds of stats in one query? As the
person_id is always the same. Thanks.

On 2010-11-16 12:06, pac Man wrote:
Use a case stmt as in:

select ...,
count( case when
creation_date > DATE_SUB(curdate(), INTERVAL 12 MONTH)
then 1 end ) as total_12_month
from stats where person_id = 1

you might want to spell out the null explicetly, but it is not necessary:

case when ... then 1 else null end


/Lennart

On 16 Nov, 12:44, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:
Works like a charm, thank you!