SQL Group by data

Question

I would like to group by Customer & Date and generate count columns for 2 separate values (Flag=Y and Flag=N). Input table looks like this:

Customer  Date   Flag
------- ------- -----
001      201201  Y
001      201202  Y
001      201203  Y
001      201204  N
001      201205  N
001      201206  Y
001      201207  Y
001      201208  Y
001      201209  N
002      201201  N
002      201202  Y
002      201203  Y
002      201205  N

The output should look like this:

Customer MinDate  MaxDate Count_Y
-------  ------  -------  -------
001      201201   201203     3   
001      201206   201208     3      
002      201202   201203     2   

How can I write the SQL query? Any kind of help is appreciated! Thanks!

Answer 1

Filter out in where Clause ,so you will get only filtered items for grouping

select 
customer,
min(date) as mindate,
max(date) as maxdate,
sum(case when flag='y' then 1 else 0 end ) count_y
from
table where flag='y'
group by
customer

Update as per Tanner comments:
since min and max won't work on your date,i recommend below..

;with cte
as
(
select *,date+'01' as newdate--now this is a valid date *yyyymmdd*
from cte
)
 select 
    customer,
    min(newdate) as mindate,
    max(newdate) as maxdate,
    sum(case when flag='y' then 1 else 0 end ) count_y
    from
    table where flag='y'
    group by
    customer