Using Distinct in Sum function

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)

Using Distinct in Sum function

Author

Topic

samwise
Starting Member

7 Posts

Posted - 2005-08-21 : 10:53:23

Hi,

I've got two columns labeled - userID,failReason.
There's a variety of users and many reasons for failure.
I'm trying to get the number of distinct failures for distinct users.
I can't distinguish the proper way of doing that.
i know i have to use distinct and sum function but i can't find the right syntax.

help needed
Thanks

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-08-21 : 12:37:18

Not sure if this is what you want, but you can return the number of distinct failures (reasons) for each user with:

SELECT
	mt.userID, 
	COUNT(DISTINCT mt.failReason) 
FROM 
	dbo.MyTable AS mt 
GROUP BY 
	mt.userID

Mark

samwise
Starting Member

7 Posts

Posted - 2005-08-22 : 03:13:46

first of all thanks for your help,
i'm not familiar with the syntax you're using.
what's the mt. for ?

Thanks,

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-22 : 03:18:35

mt is the Table's alias name
In this case it will work without that also

SELECT
userID,
COUNT(DISTINCT failReason)
FROM
dbo.MyTable AS
GROUP BY
userID

Madhivanan

Failing to plan is Planning to fail

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-08-22 : 07:31:45