on 2019 Feb 16 3:08 PM
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
81 | |
11 | |
10 | |
10 | |
10 | |
8 | |
8 | |
7 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.