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  SQL Server Development (2000)  problem with sum and query

Author  Topic  raarky Starting Member 6 Posts Posted - 2001-11-01 : 15:22:26 Hi All, I really need a bit of help with this query i'm working on at the moment. I'm trying to pull a list of users that have points allocated to them, all from various tables. The tables are userinfo, userbonus, quizscore, usersale, pointinfo. My current query sums up the point from all these tables but gets the sums wrong. basically in the quizscore and userbonus tables, if the user has mulitple points that are the same, the distinct function rips the dups out. and idea how I can get the true score? Heres my query. select u.userid userid, u.username, isnull(sum(distinct p.pointamount), 0) sale, isnull(sum(distinct b.bonusamount), 0) bonus, isnull(sum(distinct q.quizscore), 0) quizscore, isnull(sum(distinct p.pointamount), 0) + isnull(sum(distinct b.bonusamount), 0) + isnull(sum(distinct q.quizscore), 0) total from usersale s, pointinfo p, userinfo u left outer join userbonus b on u.userid=b.userid left outer join quizscore q on u.userid=q.userid where u.promoid = 3 and u.userid = s.userid and s.validsale = 1 and s.pointid = p.pointid group by u.userid, u.username order by total desc