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