problem with dups, sum and distinct

Results 1 to 2 of 2

Thread: problem with dups, sum and distinct

  1. #1
    Join Date
    Dec 1969

    Default problem with dups, sum and distinct

    Hi All,<BR><BR>I really need a bit of help with this query i&#039;m working on at the moment. I&#039;m trying to pull a list of users that have points allocated to them, all from various tables.<BR>The tables are userinfo, userbonus, quizscore, usersale, pointinfo.<BR>My current query sums up the point from all these tables but gets the sums wrong.<BR>basically in the quizscore and userbonus tables, if the user has mulitple points that are the same, the distinct function rips the dups out. if i dont use the distinct function, i get insane values that are all wrong<BR>any idea how I can get the true score?<BR>heres the db daigram<BR><BR>Heres my query.<BR><BR>select <BR>u.userid userid,<BR>u.username,<BR>isnull(sum(distinct p.pointamount), 0) sale,<BR>isnull(sum(distinct b.bonusamount), 0) bonus,<BR>isnull(sum(distinct q.quizscore), 0) quizscore,<BR>isnull(sum(distinct p.pointamount), 0) + isnull(sum(distinct b.bonusamount), 0) + isnull(sum(distinct q.quizscore), 0) total<BR><BR>from<BR>usersale s, pointinfo p, userinfo u<BR>left outer join userbonus b on u.userid=b.userid<BR>left outer join quizscore q on u.userid=q.userid<BR>where<BR>u.promoid = 3<BR>and u.userid = s.userid<BR>and s.validsale = 1<BR>and s.pointid = p.pointid<BR>group by u.userid, u.username<BR>order by total desc<BR><BR><BR>

  2. #2
    Join Date
    Dec 1969

    Default Try a union

    You could try this.<BR><BR>1. Query each table by it self.<BR>2. UNION the results so its just one recordset<BR>3. Make a VIEW of this query<BR>4. Now you can query the VIEW and GROUP or SUM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts