第一个x行数的字段总和，每个后续行偏移+1

I have a table in mysql that has 100 rows with ids that are not contiguous, but can be ordered by a date field.

I need to produce a query that that selects a sum of fields for the 10 oldest records and then a sum of fields from record 2 to record 11 and so on until i reach 90-100 these results will be grouped by the first column which is a number to show which position it is in the calculation.

ex:

Record_id   Sum(field-a) sum(field-b ) sum(field-c)  rows used:
group1      6            3             1             1-10
group2      7            3             0             2-11
group3      7            2             1             3-12

furthermore new rows are being added and these calculations will need to be recaclulated on each new row addtion taking the latest 100 rows i.e. row 1 is ignore row 101 becomes the 100th row.

hopefully that makes sense.

If this is to much for mysql, php is also an option too

I've tried some basic select statements with sums for each field , limited to 10 records or 100 records and i'm thinking about limit 10 offset 1 where offset is then incremented.

I have the option to create new tables or views.

thanks in advance any ideas or code that can help me progress.

I think I understand what you want, but your sample query references a, b, and c.

To get partial cumulative sums, you really need a sequence number. In MySQL, you get this using a correlated subquery. The rest is a self join followed by an aggregation:

select concat('group', t.seqnum), sum(t.field) as sum,
       MIN(t2.seqnum) as firstrow, max(t2.seqnum) as lastrow
from (select t.*,
             (select count(*) from t t2 where t2.date <= t.date) as seqnum
      from t
     ) t join
     (select t.*,
             (select count(*) from t t2 where t2.date <= t.date) as seqnum
      from t
     ) t2
     on t2.seqnum between t.seqnum and t.seqnum + 10
group by t.seqnum

Note: this SQL has not been tested so it might have syntax errors.

Consider this example. This query returns the sum of each row plus the 3 preceding rows. I'm using a sequential set of numbers for this illustration, but the technique will work just the same on your table...

SELECT * FROM ints;
+---+
| i |
+---+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+---+

SELECT a.i
     , SUM(b.i) ttl
  FROM 
     ( SELECT x.*, COUNT(*) rank FROM ints x JOIN ints y ON y.i <= x.i GROUP BY i ) a
  JOIN
     ( SELECT x.*, COUNT(*) rank FROM ints x JOIN ints y ON y.i <= x.i GROUP BY i ) b
    ON b.rank BETWEEN a.rank-3 AND a.rank
 GROUP 
    BY a.i;
+---+------+
| i | ttl  |
+---+------+
| 0 |    0 |
| 1 |    1 |
| 2 |    3 |
| 3 |    6 |
| 4 |   10 |
| 5 |   14 |
| 6 |   18 |
| 7 |   22 |
| 8 |   26 |
| 9 |   30 |
+---+------+