cancel
Showing results for 
Search instead for 
Did you mean: 

Sum rows two and two sql?

1,478

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

Accepted Solutions (1)

Accepted Solutions (1)

Breck_Carter
Participant
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 
0 Kudos

Works perfectly. Thanks!

Answers (0)