1

This is an perplexing SQL problem (at least to me) involving GROUP BY and AGGREGATES... would love any help.

Im working on a site that logs information about bike rides and riders. We have a table which contains a rider id, ride date, and ride distance. I want to display a table with the latest rides, and distances, as well as a total distance for each of those riders. Here is my sql and output (where id is rider id):

+--------+---------------------+----------+
| id     | dated               | distance |
+--------+---------------------+----------+
| 101240 | 2012-11-30 00:00:00 |      250 |
| 101332 | 2012-11-22 00:00:00 |       31 |
| 101313 | 2012-11-21 00:00:00 |       15 |
| 101319 | 2012-11-21 00:00:00 |       25 |
| 101320 | 2012-11-21 00:00:00 |       56 |
+--------+---------------------+----------+

This is easy to get with:

SELECT id, dated, distance FROM rides ORDER BY dated LIMIT 5

What I can't seem to figure out is getting the riders cumulative total for these most recent rides... Basically:

SELECT sum(distance) FROM rides GROUP BY id

Is it possible to handle all this in SQL without having to do something programmatic? I've tried doing some subqueries and JOINS but to no avail yet!

Thanks in advance SO community.

Justin
  • 9,634
  • 6
  • 35
  • 47
user1201372
  • 11
  • 1
  • I dont find your question very clear. "Basically: SELECT sum(distance) FROM rides GROUP BY id." what's the issue with that? – SkyWalker Dec 15 '12 at 00:24
  • If your intention is to sum distances over last five runs **per biker id** you might want to check highest ranked answer [here](http://stackoverflow.com/questions/2129693/mysql-using-limit-within-group-by-to-get-n-results-per-group). – Nikola Markovinović Dec 15 '12 at 00:40
  • so is this sql server or ? – mostafa.S Dec 15 '12 at 06:06

2 Answers2

1

Duh, I should have known my data schema a little better. I had been trying to work with the wrong id column which was actually a serialized row id, and not a riders id. A working version of SQL (on MYSQL) is:

SELECT r.rider, rr.dated, rr.distance, i.firstname, i.lastname, sum(r.distance) 
FROM rides r
INNER JOIN (SELECT rider, distance, dated FROM rides ORDER BY dated DESC LIMIT 5) rr ON r.rider = rr.rider 
INNER JOIN riders i ON r.rider = i.id 
GROUP BY r.rider ORDER BY rr.dated DESC;

This returns:

+-------+---------------------+----------+-----------+----------+-----------------+
| rider | dated               | distance | firstname | lastname | sum(r.distance) |
+-------+---------------------+----------+-----------+----------+-----------------+
|  3304 | 2012-11-30 00:00:00 |      250 | venkatesh | ss       |             250 |
|   647 | 2012-11-22 00:00:00 |       31 | ralph     | suelzle  |           22726 |
|  2822 | 2012-11-21 00:00:00 |       15 | humberto  | calderon |           10421 |
|  2339 | 2012-11-21 00:00:00 |       25 | Judy      | Rutter   |            8545 |
|  1452 | 2012-11-21 00:00:00 |       56 | Fred      | Stearley |           64366 |
+-------+---------------------+----------+-----------+----------+-----------------+

Thanks for your answers!

Hank Morton
  • 11
  • 1
0

Would something like this work? BTW, what sql server are using?

SELECT sum(distance) 
FROM (SELECT distance FROM rides ORDER BY dated DESC LIMIT 5)
Michael Dunlap
  • 4,300
  • 25
  • 36