Distinct in a group statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Distinct in a group statement

Author

Topic

adlo
Posting Yak Master

108 Posts

Posted - 2005-04-07 : 03:40:01

I have the follawing query

Select PaymentYear,PaymentMonth,Count(Member_ID)
FROM MemberPayment
GROUP BY PaymentYear,PaymentMonth

I want to do the above query to only count distinct members.

So I changed it to:
Select DISTINCT PaymentYear,PaymentMonth,Count(Member_ID)
FROM MemberPayment
GROUP BY PaymentYear,PaymentMonth

which does not work

So I had to settle for
Select PaymentYear,PaymentMonth,Count(Member_ID)
FROM (SELECT DISTINCT PaymentYear,PaymentMonth,Member_ID FROM MemberPayment)
MemberPayment
GROUP BY PaymentYear,PaymentMonth

Isn't there an easier way?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-07 : 06:08:29

Select PaymentYear,PaymentMonth,Count(DISTINCT Member_ID)
FROM MemberPayment
GROUP BY PaymentYear,PaymentMonth

Go with the flow & have fun! Else fight the flow

adlo
Posting Yak Master

108 Posts

Posted - 2005-04-07 : 06:36:20

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources