CODEWITHSUNDEEP
sqlmysqlgroup-bysum
Stephane
Stephane

Reputation: 1735

MYSQL SUM GROUP BY

I'm working on a high school grading system.

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

I have a function that properly returns averages because the most recent grade is flagged with a "current" field with a value of '1'. I'd like to make the function capable of returning the most recent grade with respect to a date in the past. I'm making a graph of how their average has changed over time.

What I'd 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't use sum and group by. It errors...

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

Upvotes: 11

Views: 68006

Answers (2)

Gaurang
Gaurang

Reputation: 1958

Try this:

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

Upvotes: -8

OMG Ponies
OMG Ponies

Reputation: 332741

GROUP BY has to come before ORDER BY:

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

Upvotes: 30

Related Questions