July 20, 2012 at 6:57 am
Hi, I perform a count on a certain condition. The results are as follows:
Count - Number of People
0 - 500
0 - 345
1 - 55
1 - 778
2 - 85
2 - 888
etc
I would like
0 - 845
1 - 833
2 - 973
Here's my code to Date:
select count(*) as count, datediff(month,con_date, first_redeem) as no_of_months_first_redeem
from klr_table
where con_date>= '2007-04-01'
group by datediff(month,con_date, first_redeem)
union all
select count(*) as count , datediff(month,convert(datetime, '2007-04-01', 120), first_redeem) as no_of_months_first_redeem
from klr_table
where con_date< '2007-04-01'
group by datediff(month,convert(datetime, '2007-04-01', 120), first_redeem)
July 20, 2012 at 7:02 am
Wrap it up inside a sub select like follows
SELECT
SUM(count),
no_of_months_first_redeem
from
(
select count(*) as count, datediff(month,con_date, first_redeem) as no_of_months_first_redeem
from klr_table
where con_date>= '2007-04-01'
group by datediff(month,con_date, first_redeem)
union all
select count(*) as count , datediff(month,convert(datetime, '2007-04-01', 120), first_redeem) as no_of_months_first_redeem
from klr_table
where con_date< '2007-04-01'
group by datediff(month,convert(datetime, '2007-04-01', 120), first_redeem)
) as dev1
group by no_of_months_first_redeem
July 20, 2012 at 7:14 am
Thanks a million for the help 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply