Adding specific every N rows in MySQL

Question

I'm trying to figure out how to select from this sample table and sum first 2 records, next two records, etc…. I have been trying to use rank and ceil but i'm clearly not managing to achieve success so thanks in advance to the human out there that can help.

recid  text1  int_value

1      Blue   3

2      Green  4

3      Yellow 6

4      Red    9

5      Purple 34

6      Black  3

to achieve desired result of summing 1st/2nd row, then 3rd/4th row, then 5th/6th row something like:

sumvalueis

7

15

37

In reality the table might have more rows but this is a sample with only 6 rows and adding int_value for 1st/2nd, 3rd/4th, 5th/6th, etc..

Firstly, there is not such a thing as `first 2 records`, nor `next` if you don't specify an order. What is that order? `recid` maybe? Secondly, there is no such a thing as `rank` in MySQL... so that's why you failed at it :) — Mosty Mostacho, Oct 25 '13 at 00:16

score 0 · Answer 1 · answered Oct 25 '13 at 00:24

Here is the query:

SELECT sum(int_value) SumValueIs FROM (
  SELECT int_value, ceil((@rank := @rank + 1) / 2) aGroup
  FROM t, (SELECT @rank := 0) init
  ORDER BY recid
) s
GROUP BY aGroup

Here is the output:

| SUMVALUEIS |
|------------|
|          7 |
|         15 |
|         37 |

Here is the fiddle.

score 0 · Answer 2 · answered Oct 25 '13 at 00:26

You could try something like this. It won't be fast but looking at your example data it should suit the purpose.

SELECT @rownum := 1,  SUM(int_value) AS sumvalueis FROM (
    SELECT t.*, @rownum := @rownum + 1 AS rank
    FROM your_table t
) AS ranked
GROUP BY FLOOR(rank/2)

Where the rank/2 is the number of rows to group.

Adding specific every N rows in MySQL

2 Answers2