COUNT help with JOIN -- sql server data warehouse

anonymous NO[at]SPAM devdex.com
7/27/2004 11:36:43 AM

Hi! I need a little help here. My set up:

2 tables:

Person.PersonID, COUNT(UserAction.ActionID)
FROM Person LEFT JOIN UserAction
ON Person.PersonID = UserAction.PersonID
GROUP BY Person.PersonID

The problem is that the UserAction table may have multiple records for a
PersonID distinguished only by another column, 'TypeID'. So, I need the
COUNT to only count UNIQUE ActionIDs.

In other words, if the recordset (ungrouped) looked like this:

PersonID, ActionID, TypeID
1 10 33
1 10 35
1 10 37
1 11 40
1 11 43

I need the resultset to be:

PersonID ActionID
1 2

Thanks for the help..

*** Sent via Developersdex http://www.developersdex.com ***

Adam Machanic
7/27/2004 2:52:41 PM

Use COUNT(DISTINCT UserAction.ActionID)

[quoted text, click to view]

sql server data warehouse : COUNT help with JOIN