Sum of Rows - SQL query

Question

I need to develop a SQL query that does the following:

        Column A     Column B

Row1    2861682      sum(Row1 to Row5)
Row2    2522226      sum(Row2 to Row5)  
Row3    2285983      sum(Row3 to Row5)
Row4    2096046      sum(Row4 to Row5)  
Row5    1935355      sum(Row5)

This is the data I have in a table column.

Now I need to calculate

sum(Row1 to Row5) 
sum(Row2 to Row5) 
sum(Row3 to Row5) 
sum(Row4 to Row5)
sum(Row5)

Thanks

What DB engine are you using? – juergen d Jul 23 '13 at 11:15 — juergen d, Jul 23 '13 at 11:15

Gordon Linoff · Accepted Answer · 2013-07-24T14:45:44.753

3

The standard SQL form for this uses a correlated subquery:

select t.*,
       (select sum(B)
        from t t2
        where t2.A >= t.A
       ) as TheSum
from t;

Some databases also support window/analytic functions that allow cumulative sums.

EDIT:

Netezza is one of the databases that support cumulative sums using window functions (thanks to its Postgres heritage, I imagine). You can also express this as:

select t.*,
       sum(B) over (order by A desc) as TheSum
from t

EDIT II:

I don't have a Netezza database to test on, but it does have the RowId column. You might be able to use this, although I cannot find any documentation that guarantees its being monotonically increasing. In fact, I'm pretty sure that for a parallel load, the rowid would not be monotically assigned.

The query would look like:

select t.*,
       sum(B) over (order by rowid desc) as TheSum
from t

edited Jul 24 '13 at 14:45

answered Jul 23 '13 at 11:19

Gordon Linoff

1,242,037
58
646
786

Can you please explain what t and B represent. B column is represented based on the summation – Gallop Jul 23 '13 at 11:29
@Moonwalk . . . `t` represents your table. `A` is the first column (seems to contain the values `row1` etc.). `B` is the second column that seems to contain the value you want to sum. SQL Tables are inherently unordered (especially on a parallel database system such as Netezza). If you don't have an identifier for the ordering (such as an id or creation date), then the cumulative sum just doesn't make sense. – Gordon Linoff Jul 23 '13 at 13:10
Hi Gordon - B is the new column.I have data only for A column . Only B column values are to be calculated. – Gallop Jul 24 '13 at 12:48
@Moonwalk . . . What column do you use to determine what row is first, second, third, and so on? SQL Tables are inherently unordered, so you need to store this information in a column. – Gordon Linoff Jul 24 '13 at 13:35
I thought I can do with a row number? I do not have a primary key column – Gallop Jul 24 '13 at 13:55
@Moonwalk . . . You don't need a primary key. You need a way of defining order. Another typical column would be a creation date. – Gordon Linoff Jul 24 '13 at 14:02

Sum of Rows - SQL query

1 Answers1