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
开发者_Go百科 GROUP BY
grades.grade_id
ORDER BY grades.date DESC
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.
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?
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;
精彩评论