Results 1 to 1 of 1
  1. #1
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187

    Unanswered: Wrong totals from LEFT JOIN

    Hi all,

    Greetings.

    I'm stucked with the sql below. I'm trying to get the total count from two scores tables (tbl_scores, tbl_scores2). Both tables have the common columns id, level and score but differ in other columns.

    Code:
    SELECT COUNT(*) AS done, AVG(score) AS avg_score
          FROM tbl_scores
          LEFT JOIN tbl_scores2
            ON tbl_scores.id=tbl_scores2.id
          WHERE tbl_scores.member_d.id=?
          AND tbl_scores.level=?
          HAVING avg_score > 70
          AND done > 100
     };
    Update: The above sql is flawed because "score" is ambiguous

    I'm getting the wrong totals for count. Let's say I've 10 rows from "tbl_scores" and 5 rows from "tbl_scores2" for a given member_id, instead of getting 15, I'm getting a bigger total.

    Is my sql flawed?

    Thanks in anticipation

    Question Reprase: How do I get the average value of an item from two tables, accompanied by a count of the selected item from both tables?

    The following sql gets me the respective totals and counts:

    Code:
    SELECT COUNT(*) AS done, SUM(score) AS total
      FROM test_scores1
      WHERE test_scores1.member_id=1
      AND test_scores1.level='5'
      UNION ALL SELECT COUNT(*) AS done2, SUM(score) AS total2
      FROM test_scores2
      WHERE test_scores2.member_id=1
      AND test_scores2.level='5'
    
    #Result of query:
    
    done | total
    6    | 480
    3    | 200
    That gives a grand total of 680 and an average of 75.5.

    Is there a way to get the value of 75.5 (the aggregate average) and the total count of 9 from the two tables?
    Last edited by pearl2; 12-16-05 at 07:13.

Posting Permissions

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