PHP/mysql problem with output of query

Question

I have this table in my database:

classes

id | classname | 


grades 

id | class_id | grade | date

I need to get the average of the grades and the latest grade based on the date + the correct classname.

I have this query but this doesn't output the correct classnames...

SELECT 
                AVG(grade),
                classes.classname,
                grades.grade,
                MIN(ABS(DATEDIFF(CURDATE(),'date')))
                FROM 
                grades, classes

                GROUP BY
                grades.grade_id

                ORDER BY grades.date DESC

I need to get a output like English | 8.0 (average) | 7.5 (latest) — DaDu
– DaDu, Commented Apr 7, 2011 at 14:18
Huh, what is cijfers? You aren't selecting from a table with this name... — schizodactyl
– schizodactyl, Commented Apr 7, 2011 at 14:18
fixt the cijfer.datum, i translated the names.. (really badly though) — DaDu
– DaDu, Commented Apr 7, 2011 at 14:21

BugFinder · Accepted Answer · 2011-04-07 14:22:56Z

1

Your question seemed a little vauge, so Im answering my interpretation of your question.

You didnt demonstrate what you got, but, Im going to guess you got a list of grades with an average by class, not classes with grades..

So, while I think theres a few questions in there. First.

select avg(grade), classname from classes 
left join grade on classes.id = grades.class_id 
group by classname

Should give you the average grade by class.

What is cijfers? you didnt really include much about that yet you also asked it to average it somehow.

answered Apr 7, 2011 at 14:22

BugFinder

17.9k4 gold badges42 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Berry Langerak Over a year ago

"cijfers" is the Dutch word for grades. :) Your solution should be correct; the issue is that the group by can't work.

DaDu Over a year ago

Correct, and I can't explain my problem that well in english. sorry fot that.

SimonDowdles · Accepted Answer · 2011-04-07 14:27:18Z

0

First off, AVG(cijfer) is actually referencing nothing. You need to assign the AVG() to a variable like this:

AVG(grade) AS avgrade

Perhaps you could try a query like this:

SELECT AVG(grade) as average FROM grades, class_id AS cid, (SELECT classname FROM classes WHERE id=cid) AS cname FROM grades ORDER BY date DESC GROUP BY cname;

Hows that work out?

edited Apr 7, 2011 at 14:27

answered Apr 7, 2011 at 14:21

SimonDowdles

2,0446 gold badges24 silver badges36 bronze badges

1 Comment

SimonDowdles Over a year ago

updated answer as I forgot to assign class name to a variable

Ethan Brown · Accepted Answer · 2011-04-07 14:40:52Z

0

As much as I hate doing someone's homework for them, This seems to work for me:

select a.classname, AVG(b.grade) AS "Avg Grade",
       c.grade as "Most Recent Grade", c.grade_date as "Most Recent Grade Date"
from classes a, grades b, grades c
where a.id = b.class_id
AND c.class_id = a.id
AND NOT EXISTS
    (SELECT d.grade_date FROM grades d
     WHERE d.class_id = c.class_id
     AND   d.grade_date > c.grade_date)
group by a.classname, c.grade, c.grade_date;

edited Apr 7, 2011 at 14:40

answered Apr 7, 2011 at 14:29

Ethan Brown

7136 silver badges12 bronze badges

Collectives™ on Stack Overflow

PHP/mysql problem with output of query

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related