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

asked 16 Feb, 15:08

Rolle's gravatar image

Rolle
492343852
accept rate: 0%


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 
permanent link

answered 16 Feb, 15:39

Breck%20Carter's gravatar image

Breck Carter
29.1k482647949
accept rate: 21%

Works perfectly. Thanks!

(16 Feb, 15:58) Rolle
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×96

question asked: 16 Feb, 15:08

question was seen: 138 times

last updated: 16 Feb, 15:58