How can i get the data as below using sql

  • Hi ...I am a newbie to SQL.......Help me with the below req

    for the table below

    drop table sample_data

    go

    create table sample_data

    (

    rn int ,

    dur float

    )

    GO

    insert into sample_data select 1,1.00

    insert into sample_data select 2,2.00

    insert into sample_data select 3,3.00

    GO

    SELECT * FROM sample_data

    GO

    i need a new column with the last row should be 21 (Fixed number for the last row) - 3 (dur) = 18

    2nd row last col shloud be 18 (result of 3rd row) - 2 (dur) = 16

    3rd row last col shloud be 16 (result of 2nd row) - 1 (dur) = 15

    |rn|dur|calculated_col|

    |1  |1       |15|

    |2 |2      |16|

    |3 |3       |18|

    Thanking you in advance

     

  • SELECT rn,dur,21-sum(dur) over(order by rn desc) as calculated_col
    FROM sample_data
    order by rn;

    • This reply was modified 1 month, 1 week ago by  Mark Cowne.

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • aggy_02_20 wrote:

    Hi ...I am a newbie to SQL.......Help me with the below req for the table below

    drop table sample_data go create table sample_data ( rn int , dur float ) GO

    insert into sample_data select 1,1.00 insert into sample_data select 2,2.00 insert into sample_data select 3,3.00 GO

    SELECT * FROM sample_data GO

    i need a new column with the last row should be 21 (Fixed number for the last row) - 3 (dur) = 18

    2nd row last col shloud be 18 (result of 3rd row) - 2 (dur) = 16 3rd row last col shloud be 16 (result of 2nd row) - 1 (dur) = 15

    |rn|dur|calculated_col| |1  |1       |15| |2 |2      |16| |3 |3       |18|

    Thanking you in advance

     

    What does the column "dur" represent?  I would likely recommend not using a float.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply