MYSQL SUM GROUP BY

Question

I am working on a high school grading system.

At my school, grades can be changed by redoing problems, and I save these changes with dates.

I have a function that correctly returns averages because the last class is marked as the "current" field with a value of "1". I would like a function to be able to return the last class with respect to a date in the past. I am plotting how their average has changed over time.

What I would like to do is something like this:

select sum(grades.points) from grades where date < 'thedate' order by date DESC group by assignmentID 

I can not use sum and group by. These are mistakes ...

The best I can think of is to make a sub-selection. Any other thoughts?

Answer 1

GROUP BY must pass before ORDER BY:

  SELECT SUM(g.points) FROM GRADES g WHERE g.date < 'thedate' GROUP BY g.assignmentid ORDER BY g.date DESC 

Answer 2

Try the following:

 SELECT cat_name, SUM(amount) AS total_amount FROM table GROUP BY cat_name