Amrit kr sh Amrit kr sh - 2 months ago 11
SQL Question

Selecting sum of three consecutive values in a same row in the table in MYSQL

plz refer the table to provide the sql query to get result like that.

id value
1 10
2 15
3 30
4 10
5 11
6 12


Desired output:

id value
1 55
2 33


http://sqlfiddle.com/#!9/21cbc8

Answer

Divide id by 3, round it up, group it (with a sum):

SELECT 
    ceiling(id / 3) AS NewID, 
    sum(Value)      AS SumValue
FROM MyTable
GROUP BY ceiling(id / 3)

With a variable:

SET @GroupVar = 3;  -- Set this number to whatever you want to group by

SELECT 
    ceiling(id / @GroupVar) AS NewID, 
    sum(Value)      AS SumValue
FROM MyTable
GROUP BY ceiling(id / @GroupVar);