Group by every n rows in MySQL

Question

Given a MySql table, is there a way to use Group By to group every n rows, in the same way a Group By normally would?

Say we have rows like:

• A, 12
• B, 14
• C, 20
• D, 78
• E, 90
• F, 65

I want Group By n = 2 to return

• A, 12
• C, 20
• E, 90

Since each bucket contains 2 rows, like a normal Group By.

After grouping like this, I am going to run another query that will return say, the sum of all the second column in every group, so the end result rows would look like:

Group	Sum
1	26
2	98
3	155

Answer 1

For the first query, you may use ROW_NUMBER with the modulus:

WITH cte AS (
    SELECT *, (ROW_NUMBER() OVER (ORDER BY id) - 1) % 2 rem
    FROM yourTable
)

SELECT id, val
FROM cte
WHERE rem = 0;

Demo

For the second query, we can use a similar approach with integer division

WITH cte AS (
    SELECT *, FLOOR((ROW_NUMBER() OVER (ORDER BY id) - 1) / 2) dvd
    FROM yourTable
)

SELECT dvd + 1 AS grp, SUM(val) AS val_sum
FROM cte
GROUP BY dvd;

Demo