[SQL] Performing a distinct count AND taking a sum..

[Stompy]

The following query simply takes each user from the users table and gets the visitor count (joined w/ user ID), transaction count (joined w/ user ID), and finally, the sum of all transaction amounts for that user ID.

The problem I'm having is that the query gets the distinct counts just fine for the visitors and transactions, but when I try to sum the "Amount", it ends up multiplying it by the Visitor count. I'm not actually Grouping By the Visitor ID, so where does it get the idea that it should multiply the two together?

Example:

SELECT     
	dbo.Users.UserID, 
	dbo.Users.UserName, 
	COUNT(DISTINCT dbo.Visitors.VisitorID) AS Visitors, 
	COUNT(DISTINCT dbo.Trans.TransID) AS Trans, 
	SUM(dbo.Trans.Amount) AS Amount
FROM         
	dbo.Users LEFT OUTER JOIN dbo.Visitors ON dbo.Users.UserID = dbo.Visitors.UserID 
		LEFT OUTER JOIN dbo.Trans ON dbo.Users.UserID = dbo.Trans.UserID
GROUP BY 
	dbo.Users.UserID, dbo.Users.UserName

Returns the results:

UserID      UserName                                           Visitors    Trans       Amount                
----------- -------------------------------------------------- ----------- ----------- --------------------- 
1           Test                                               5           2           10.0000
2           Test 1                                             4           1           20.0000
3           Test 2                                             3           1           30.0000

The results I'm aiming to have returned are:

UserID      UserName                                           Visitors    Trans       Amount                
----------- -------------------------------------------------- ----------- ----------- --------------------- 
1           Test                                               5           2           2.0000
2           Test 1                                             4           1           5.0000
3           Test 2                                             3           1           10.0000

The 3 tables I'm using are Users, Trans, and Visitors respectively:

UserID      UserName                                           
----------- -------------------------------------------------- 
1           Test
2           Test 1
3           Test 2

(3 row(s) affected)

TransID     Amount                UserID      
----------- --------------------- ----------- 
1           1.0000                1
2           1.0000                1
3           5.0000                2
4           10.0000               3

(4 row(s) affected)

VisitorID   UserID      
----------- ----------- 
1           1
2           1
3           1
4           1
5           1
6           2
7           2
8           2
9           2
10          3
11          3
12          3

(12 row(s) affected)

[edit]
I could get around it by changing the amount to be computed like:

SUM(dbo.Trans.Amount)/COUNT(DISTINCT dbo.Visitors.VisitorID) AS Amount

...but I'm sure it's something I'm overlooking in my statement.

Any ideas? Your help is greatly appreciated

[crazymang]

check out the compute clause

[magua]

Originally posted by Stompy
The problem I'm having is that the query gets the distinct counts just fine for the visitors and transactions, but when I try to sum the "Amount", it ends up multiplying it by the Visitor count. I'm not actually Grouping By the Visitor ID, so where does it get the idea that it should multiply the two together?

The JOINs are having multiple rows returned, and the SUM is dutifully adding them up. So, eg, if User1 appears in the Visitors table 5 times, joining the Users and Visitors table together returns five records. SUM() will sum all five.

Most likely, you can use SUM(DISTINCT dbo.Trans.Amount). Alternatively, it may be better to include the Visitors part as a subquery, instead of JOINing it:

SELECT Users.UserID, Users.UserName, (SELECT COUNT(Visitors.VisitorID) FROM Visitors WHERE Visitors.UserID = Users.UserID), COUNT(Trans.TransID), SUM(Trans.Amount)
FROM Users
LEFT OUTER JOIN dbo.Trans ON Users.UserID = Trans.UserID
GROUP BY Users.UserID, Users.UserName