How can i get the data as below using sql

Question

How can i get the data as below using sql

aggy_02_20

SSC Enthusiast

Points: 176
More actions
March 3, 2021 at 9:35 am
Go to Answer
#3852768

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
Mark Cowne

One Orange Chip

Points: 26944
More actions
March 3, 2021 at 9:54 am
Answer
#3852772
```
SELECT rn,dur,21-sum(dur) over(order by rn desc) as calculated_col
FROM sample_data
order by rn;
```
- This reply was modified 3 years, 1 month 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

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

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

Michael L John One Orange Chip Points: 27038 More actions · Answer 1

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/