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:
Code:
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:
Code:
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:
Code:
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:
Code:
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:
Code:
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
