Tilted Forum Project Discussion Community - [SQL] Performing a distinct count AND taking a sum..

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