I have a sql command that selects AVG score from two tables, php/mysql, but it returns empty?

Question

I have a sql command that selects AVG score from two tables, php/mysql, but it returns empty if one of the tables dosnt have a score for the employee..

so here it is 

SELECT (
 (SUM(QA_Scores.score_total) + SUM(qaform.qscore)) / (COUNT(QA_Scores.score_total) + COUNT(qaform.qscore)
)
) AS avg_score
	FROM QA_Scores
	LEFT JOIN qaform ON QA_Scores.score_tech = qaform.emp_id
	WHERE qaform.emp_id = XXXXXX

Now, it does work perfectly  IF THERE ARE SCORES ON BOTH TABLES

but the problem I am having is that there may not be a score on one of the tables.. it could be one or the other

Any workarounds?

Thank you

Shawn

Ratchetr · Accepted Answer

You could do it as the average of all the scores from the first table + all the scores in the second table for employee XXXXX:

SELECT AVG(s.score) avg_score
FROM
(
SELECT score_total FROM QA_Scores WHERE score_tech = XXXXXX
UNION
SELECT qscore FROM qaform WHERE emp_id = XXXXXX
)s

ETA:
Take 2:
SELECT AVG(s.score) avg_score
FROM
(
SELECT score_total score FROM QA_Scores WHERE score_tech = XXXXXX
UNION ALL
SELECT qscore score FROM qaform WHERE emp_id = XXXXXX
)s

Add the score alias, and changed UNION to UNION ALL (Without ALL, duplicate rows would be removed, which would give the wrong result)

fullem · Answer

Assuming that, based on your schema, a action picture is rented provided that it has a row in mm_rental (which probably skill it relatively is deleted from there while back), you will get a itemizing of obtainable video clips from go with movie_id, movie_title FROM mm_movie the place movie_id not IN (go with movie_id FROM mm_rental)

Trending News

I have a sql command that selects AVG score from two tables, php/mysql, but it returns empty?

2 Answers