MySQL sum of query result

Question

I have a table called "trails" inside the table I have 3 columns called "id", "name" and "grading".

I have the following query which gives me the last 5 rows of a specific trail name based on the id. This works perfectly but now I need to get the sum total of the 5 results in the grading column?

SELECT * FROM `trails` WHERE `name` = "Free Flow" ORDER BY `id` DESC LIMIT 5

As a side note, ordering by an id column is often meaningless. It might be the insertion order, but this isn't actually guaranteed. And since db ids have no real meaning to the world outside, why would anybody else care about ordering by id? What do you mean by "last 5 rows"? Last 5 inserted - do you have a timestamp to record this? Last 5 with activity (ie, a row was updated, changing grading)? Rely on ids for only one thing - uniqueness (that is, only use them in ORDER BYs as a way to make sorts stable - you should have something else as the first sort column). — Clockwork-Muse
– Clockwork-Muse, Commented Jul 8, 2014 at 8:30
There will be no updating of rows, only want the last 5 entries which will have a unique incrementing id. It works fine for this small application, thanks for the note though. — user1287814
– user1287814, Commented Jul 8, 2014 at 9:24

Sadikhasan · Accepted Answer · 2014-07-08 08:20:18Z

2

SELECT sum(`grading`)
FROM
  (SELECT *
   FROM `trails`
   WHERE `name` = "Free Flow"
   ORDER BY `id` DESC LIMIT 5) AS TEMP;

answered Jul 8, 2014 at 8:20

Sadikhasan

18.6k23 gold badges84 silver badges126 bronze badges

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

Comments

manlio · Accepted Answer · 2014-07-08 08:40:05Z

-1

you can use this query to get the sum of a particular column

SELECT SUM(originalOfferSize)
FROM `ActiveListings`
WHERE `originalOfferSize` = 4000000
ORDER BY `listingId` DESC LIMIT 5

edited Jul 8, 2014 at 8:40

manlio

19.1k14 gold badges83 silver badges139 bronze badges

answered Jul 8, 2014 at 8:17

user2473917

1

2 Comments

Cthulhu Over a year ago

I strongly suspect it will sum all rows because groupping happens before limiting (i'm not so sure so i didn't downvote).

Cthulhu Over a year ago

@user2473917 It must be, I think it wouldn't be accepted otherwise.

Collectives™ on Stack Overflow

MySQL sum of query result

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related