AVG giving wrong results in one query but correct in other query

Databases
1

Hello all,
I am trying to get a percentage average from student grades.
This query is giving accurate results.

$sth = $dbh->prepare('SELECT AVG(gd.percent) AS classAvg
, st.first_name
FROM students AS st
INNER JOIN
grades AS gd
ON
gd.student_id = st.id
	
WHERE gd.gradelevel BETWEEN :low AND :high GROUP BY gd.class');

the correct result is 46.6667, verified by calculator.
when I run this query,

$sth = $dbh->prepare('SELECT st.first_name
, st.last_name
, gd.gradelevel
, gd.percent
, gda.classav
FROM students AS st
INNER JOIN
grades AS gd
ON
gd.student_id = st.id
INNER JOIN
(
	SELECT      AVG(percent) as classav
	FROM        grades
	GROUP BY    class
) AS gda ON gd.student_id = st.id
	
WHERE gd.gradelevel BETWEEN :low AND :high');

my result is [classav] => 45.8571

I have spent hours as in lots of hours and now I turn to you.
One clue I have is that AVG (from what I Googled) needs a GROUP BY clause and if it is removed in this query, the same results, 45.8571.
All students have grades and grades need to be between :low and :high.

Thank you for any and all help.

2

looks like an incorrect join right there

3

if I try gda.student_id instead of , gd.student_id, big errors, expected that.
add to first select , gda.student_id
add to second select student_id
result is [classav] => 45.8571.
even tried,

AS gda ON gda.student_id = gd.student_id

No joy still 45.8571, it has to be averaging something
I think it is a case of too many trees to see the forest.

4

the gda subquery has only the average in its SELECT list

that’s the incorrect join – you have to SELECT the column you’re gonna join on

5

I must be missing something, I need the average from the percent column.
Do I need the alias gd.percent? all examples I have seen don’t use the alias.

6

could you please post your latest query

7

Thank you Rudy
Here is what I have now, I have tried this before so the expected results are incorrect.

SELECT st.first_name
, st.last_name
, gd.gradelevel
, gd.percent
, gda.classav
, gda.student_id
FROM students AS st
INNER JOIN
grades AS gd
ON
gd.student_id = st.id
INNER JOIN
(
	SELECT      student_id
	, AVG(percent) as classav
	FROM        grades
	
) AS gda ON gda.student_id = st.id

WHERE gd.gradelevel BETWEEN :low AND :high

This is the gotcha, “AS gda ON gda.student_id = st.id” returns only one record.

        [first_name] => loren
        [last_name] => wolsiffer
        [gradelevel] => 9
        [percent] => 42
        [classav] => 45.8571
        [student_id] => 2

It gives my percent and apparently averages something.

“AS gda ON gd.student_id = st.id” pulls the correct number of records and all show average as 45.8571

It was late last night and fell asleep.
I really thought 45.8571 was the correct result but I double checked it and should be 46.666666666667.
Thank you so much.

8

subquery requires a GROUP BY clause

9

As I mentioned before same results with or without GROUP BY class.

10

Does the where clause need to be inside the sub-query as well? You may be including more records in the sub-query than you’re expecting to.

2 Likes
11

You nailed it, Thank you. Valuable lesson to put in my hat.
Thank you, Thank you, Thank you

1 Like
12

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.