SQL: SUM after GROUP BY

Question

Sample Table

CustomerId | VoucherId | CategoryId | StartDate | EndDate
-------------------------------------------------------------
        10 |         1 |          1 | 2013-09-01| 2013-09-30
        10 |         1 |          2 | 2013-09-01| 2013-09-30
        11 |         2 |          1 | 2013-09-01| 2013-11-30
        11 |         2 |          2 | 2013-09-01| 2013-11-30
        11 |         2 |          3 | 2013-09-01| 2013-11-30
        10 |         3 |          1 | 2013-10-01| 2013-12-31
        10 |         3 |          2 | 2013-10-01| 2013-12-31
        11 |         4 |          1 | 2013-12-01| 2014-04-30

In above sample record, I want to find out total No. of months customer's vouchers cover

I need the output in the form

CustomerId | Months
--------------------
        10 | 4
        11 | 8

The catch is that a voucher can have multiple rows for different CategoryIds...

I calculated months covered by a voucher as DATEDIFF(MM, StartDate, EndDate) + 1...

When i apply SUM(DATEDIFF(MM, StartDate, EndDate)) GROUP BY VoucherId, StartDate, EndDate I give wrong result because of multiple rows for a VoucherId....

I get something like this...

CustomerId | Months
--------------------
        10 | 8
        11 | 14

CategoryId is useless in this scenario

Thanks

Answer 1

actually, in your case (when periods for each category are equal) you can use this query:

with cte as (
    select distinct
        CustomerId, StartDate, EndDate
    from Table1
)
select CustomerId, sum(datediff(mm, StartDate, EndDate) + 1) as diff
from cte
group by CustomerId

sql fiddle demo