CODEWITHSUNDEEP
sqlamazon-redshift
Reise45
Reise45

Reputation: 1203

SQL: Add the row values and display in new column

I Have table t1 ordered by tasteRating

    Fruit | tasteRating|Cost 
    -----------------------
    Apple |  99       | 1  
    Banana|  87       | 2  
    Cherry|  63       | 5

I want t2

    Fruit | Cost | Total Cost
    -------------------------
    Apple |   1  | 1
    Banana|   2  | 3
    Cherry|   5  | 8

Is there a way to generate Total Cost dynamically in SQL based on value of Cost? Doing this on Redshift. Thanks

Upvotes: 3

Views: 6663

Answers (1)

user330315
user330315

Reputation:

A running sum like that, can easily be done in a modern DBMS using window functions:

select col_1,
       sum(col_1) over (order by taste_rating desc) as col_2
from the_table;

Note however that a running sum without an order by doesn't make sense. So you have to include a column that defines the order of the rows.

SQLFiddle: http://sqlfiddle.com/#!15/166b9/1

EDIT: (By Gordon)

RedShift has weird limitations on Window functions. For some reason, it requires the rows between syntax:

sum(col_1) over (order by taste_rating desc
                 rows between unbounded preceding and current row
                ) as col_2

I have no idea why it has this requirement. It is not required by ANSI (although it is supported) and it is not a limitation in Postgres (the base database for Redshift).

Upvotes: 8

Related Questions