|
I have a table with row type 0 and 1. Now I want to sum all 0 and 1. That is, I want to summarize the rows two and two. Row 0 and 1 belong together. Row 2 and 3 belong together etc. I have been thinking, but I do not understand how to solve this in a simple way? I have this: RowType Component Amount RowIndex ---------------------------------------- 0 Mat1 24 0 1 Mat9 43 1 0 Mat2 21 2 1 Mat2 13 3 0 Mat3 41 4 1 Mat1 31 5 0 Mat5 19 6 1 Mat5 11 7 0 Mat4 14 8 1 Mat1 12 9 I want to add one a field with sql that summarizes the rows two and two. 0 and 1 together. Like this: RowType Component Amount RowIndex Sum ---------------------------------------------------- 0 Mat1 24 0 24 1 Mat9 43 1 67 <-- sum 0 Mat2 21 2 21 1 Mat2 13 3 34 <-- sum 0 Mat3 41 4 41 1 Mat1 31 5 72 <-- sum 0 Mat5 19 6 19 1 Mat5 11 7 30 <-- sum 0 Mat4 14 8 14 1 Mat1 12 9 26 <-- sum |
CREATE TABLE t (
"RowType" INTEGER,
Component VARCHAR ( 10 ),
Amount INTEGER,
RowIndex INTEGER );
INSERT t VALUES ( 0, 'Mat1', 24, 0 );
INSERT t VALUES ( 1, 'Mat9', 43, 1 );
INSERT t VALUES ( 0, 'Mat2', 21, 2 );
INSERT t VALUES ( 1, 'Mat2', 13, 3 );
INSERT t VALUES ( 0, 'Mat3', 41, 4 );
INSERT t VALUES ( 1, 'Mat1', 31, 5 );
INSERT t VALUES ( 0, 'Mat5', 19, 6 );
INSERT t VALUES ( 1, 'Mat5', 11, 7 );
INSERT t VALUES ( 0, 'Mat4', 14, 8 );
INSERT t VALUES ( 1, 'Mat1', 12, 9 );
COMMIT;
SELECT *,
IF "RowType" = 0
THEN Amount
ELSE FIRST_VALUE ( Amount ) OVER two_rows + Amount
END IF AS "Sum"
FROM t
WINDOW two_rows AS ( ORDER BY t.RowIndex ROWS BETWEEN 1 PRECEDING AND CURRENT ROW )
ORDER BY RowIndex;
RowType Component Amount RowIndex Sum
----------- ---------- ----------- ----------- -----------
0 Mat1 24 0 24
1 Mat9 43 1 67
0 Mat2 21 2 21
1 Mat2 13 3 34
0 Mat3 41 4 41
1 Mat1 31 5 72
0 Mat5 19 6 19
1 Mat5 11 7 30
0 Mat4 14 8 14
1 Mat1 12 9 26
Works perfectly. Thanks!
(16 Feb '19, 15:58)
Rolle
|
