CODEWITHSUNDEEP
sqlsql-server-2005
Goolsy
Goolsy

Reputation: 237

Number of Sundays in a list of dates

I have a list of members of a club together with dates that they attended the club. I need to know how many Sundays did each member attend over a given period. I have a table that lists each attendance, made up of member number and the attendance date.

Eg In this example 13/1 and 20/1 are Sundays

MEMBER                 ATTENDANCE

12345                  13/1/13

12345                  14/1/13

56789                  13/1/13

56789                  14/1/13

56789                  20/1/13

24680                  14/1/13

24680                  15/1/13

Ideally I would like to see this returned:

MEMBER                 # OF SUNDAYS

12345                  1

56789                  2

24680                  0

Upvotes: 0

Views: 60

Answers (1)

Madhivanan
Madhivanan

Reputation: 13700

select MEMBER, 
sum(case when datename(weekday,ATTENDANCE)='Sunday' then 1 else 0 end) as no_of_sundays 
from table 
group by MEMBER

EDIT : To handle duplicate dates, use

select MEMBER, 
sum(case when datename(weekday,ATTENDANCE)='Sunday' then 1 else 0 end) as no_of_sundays 
from (select distinct MEMBER,ATTENDANCE from table ) as t 
group by MEMBER

Upvotes: 3

Related Questions