GROUP BY ID range?
- by d0ugal
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.
