Wrong totals from LEFT JOIN

pearl2 · #1 (**permalink**) 12-16-05, 04:17

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?

Internet Services

Web Development

Digital Marketing

Consumer Tech