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.
SELECT COUNT(*) AS done, AVG(score) AS avg_score
LEFT JOIN tbl_scores2
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:
SELECT COUNT(*) AS done, SUM(score) AS total
UNION ALL SELECT COUNT(*) AS done2, SUM(score) AS total2
#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?