GROUP BY ID range?

Posted by d0ugal on Stack Overflow See other posts from Stack Overflow or by d0ugal
Published on 2009-07-11T15:00:25Z Indexed on 2010/03/20 14:31 UTC
Read the original article Hit count: 241

Given a data set like this;

+-----+---------------------+--------+
| id  | date                | result |
+-----+---------------------+--------+
| 121 | 2009-07-11 13:23:24 |     -1 | 
| 122 | 2009-07-11 13:23:24 |     -1 | 
| 123 | 2009-07-11 13:23:24 |     -1 | 
| 124 | 2009-07-11 13:23:24 |     -1 | 
| 125 | 2009-07-11 13:23:24 |     -1 | 
| 126 | 2009-07-11 13:23:24 |     -1 | 
| 127 | 2009-07-11 13:23:24 |     -1 | 
| 128 | 2009-07-11 13:23:24 |     -1 | 
| 129 | 2009-07-11 13:23:24 |     -1 | 
| 130 | 2009-07-11 13:23:24 |     -1 | 
| 131 | 2009-07-11 13:23:24 |     -1 | 
| 132 | 2009-07-11 13:23:24 |     -1 | 
| 133 | 2009-07-11 13:23:24 |     -1 | 
| 134 | 2009-07-11 13:23:24 |     -1 | 
| 135 | 2009-07-11 13:23:24 |     -1 | 
| 136 | 2009-07-11 13:23:24 |     -1 | 
| 137 | 2009-07-11 13:23:24 |     -1 | 
| 138 | 2009-07-11 13:23:24 |      1 | 
| 139 | 2009-07-11 13:23:24 |      0 | 
| 140 | 2009-07-11 13:23:24 |     -1 | 
+-----+---------------------+--------+

How would I go about grouping the results by day 5 records at a time. The above results is part of the live data, there is over 100,000 results rows in the table and its growing. Basically I want to measure the change over time, so want to take a SUM of the result every X records. In the real data I'll be doing it ever 100 or 1000 but for the data above perhaps every 5.

If i could sort it by date I would do something like this;

SELECT
   DATE_FORMAT(date, '%h%i') ym,
   COUNT(result) 'Total Games', 
   SUM(result) as 'Score'
FROM nn_log
GROUP BY ym;

I can't figure out a way of doing something similar with numbers. The order is sorted by the date but I hope to split the data up every x results. It's safe to assume there are no blank rows.

Doing it above with the data you could do multiple selects like;

SELECT SUM(result) FROM table LIMIT 0,5;
SELECT SUM(result) FROM table LIMIT 5,5;
SELECT SUM(result) FROM table LIMIT 10,5;

Thats obviously not a very good way to scale up to a bigger problem. I could just write a loop but I'd like to reduce the number of queries.

© Stack Overflow or respective owner

Related posts about mysql

Related posts about mysql-query