SELECT SUM of each row and the next row

Question

Table three columns id, numers1 and numbers2. We need to summarize numers1 and numbers2 but the first row to the second row numers1 numers2 the second with the third and forth etc.:

CREATE TABLE tb1 (id INTEGER PRIMARY KEY AUTOINCREMENT,numbers1,numbers2);
INSERT INTO tb1 (numbers1,numbers2) values(1,10);
INSERT INTO tb1 (numbers1,numbers2) values(2,20);
INSERT INTO tb1 (numbers1,numbers2) values(3,30);
INSERT INTO tb1 (numbers1,numbers2) values(4,40);
INSERT INTO tb1 (numbers1,numbers2) values(5,50);

I want to get as:

CREATE TABLE tb1 (id INTEGER PRIMARY KEY AUTOINCREMENT,numbers1,numbers2); INSERT INTO TABLE_NAME (numbers1,numbers2) values(1,10); INSERT INTO TABLE_NAME (numbers1,numbers2) values(2,20); INSERT INTO TABLE_NAME (numbers1,numbers2) values(3,30); INSERT INTO TABLE_NAME (numbers1,numbers2) values(4,40); INSERT INTO TABLE_NAME (numbers1,numbers2) values(5,50); I want to get as 21 32 43 54 — shalx gobe, Feb 17 '17 at 06:58

score 0 · Answer 1 · edited May 23 '17 at 12:24

with the reference of getting the correct row index per record here: How to use ROW_NUMBER in sqlite

I was able to create the required result with the following query:

SELECT
num1 + coalesce(b_num2, 0)
FROM(
SELECT 
num1,
(select count(*) from test as b  where a.id >= b.id)  as cnt
FROM test as a) as a
LEFT JOIN 
(SELECT num2 as b_num2, 
(select count(*) from test as b  where a.id >= b.id)  as cnt
 FROM test as a 
) as b
ON b.cnt = a.cnt + 1

Explanation:

by joining two same table of similar record index, then merge the next record with the current record and then sum num1 of current record with num2 of next record, I do not know how you want to deal with the last row as it does not have a next row so I assume it to add nothing to have a result of just the value of num1

Result:

Thanks for the quick response, it is too difficult for me and I will do the best php script — shalx gobe, Feb 17 '17 at 08:06

score 0 · Answer 2 · answered Feb 17 '17 at 09:53

For one row with a specific ID x, you can get values from the next row by searching for ID values larger than x, and taking the first such row:

SELECT ...
FROM tb1
WHERE id > x
ORDER BY id
LIMIT 1;

You can then use this as a correlated subquery to get that value for each row:

SELECT numbers1 + (SELECT T2.numbers2
                   FROM tb1 AS T2
                   WHERE T2.id > T1.id
                   ORDER BY T2.id
                   LIMIT 1) AS sum
FROM tb1 AS T1
WHERE sum IS NOT NULL;  -- this omits the last row, where the subquery returns NULL

SELECT SUM of each row and the next row

2 Answers2