Answered by:
SQL Server sum field from previous calculation
Question

User1575600908 posted
In SQL Server, I have table with 4 column
artid num A B 46 1 417636000 0 47 1 15024000 0 102 1 3418105650 0 226 1 1160601286 0 60 668 260000 0 69 668 5500000 0
I want in result set create new column for some calculation
This column should have value like this:
artid num a b newColumnValue      46 1 417636000 0 ab+previous newColumnValue
I write this query, but I can't get
previous newColumnValue
:select *, (a b+ lag(a b, 1, a b) over (order by num,artid)) as newColumnValue FROM MainTbl ORDER BY num,artid
i get this result
artid num a b newColumnValue      46 1 417636000 0 417636000 47 1 15024000 0 432660000 102 1 3418105650 0 3433129650 226 1 1160601286 0 4578706936 60 668 260000 0 1160861286 69 668 5500000 0 5760000
i want get this result
artid num a b newColumnValue      46 1 417636000 0 417636000 47 1 15024000 0 432660000 102 1 3418105650 0 3850765650 226 1 1160601286 0 5011366936 60 668 260000 0 5011626936 69 668 5500000 0 5017126936
can you help me?
Saturday, December 9, 2017 2:56 PM
Answers

User991499041 posted
Hi Sadeq.hatami,
Use
sum(ab)
, not(a b+ lag(a b, 1, a b)
declare @tb table(artid int,num int,A decimal,B decimal) insert into @tb values (46,1,417636000,0), (47,1,15024000,0), (102,1,3418105650 ,0), (226,1,1160601286,0), (60,668,260000,0), (69,668,5500000,0) select *,sum(AB) Over(Order by num,artid) as newColumnValue from @tb
Screenshot
Regards,
zxj
 Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Wednesday, December 13, 2017 7:31 AM 
User991499041 posted
Hi Sadeq.hatami,
thank's for your answer, can you explain me
sum(AB) Over(Order by num,artid)
what exactly does it do? i ca'nt understand it
Add an order by to the Over() and it will do a cumulative sum of the previous rows.
It means
newColumnValue
column is a cumulative sum of the (AB) for the previous demand orders.Cumulative sum of previous rows
http://www.sqlservercentral.com/articles/cumulative/109158/
Calculate cumulative sum of previous rows in SQL Server
http://sqlindia.com/calculatecumulativesumofpreviousrowssqlserver/
Regards,
zxj
 Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Wednesday, December 13, 2017 8:38 AM
All replies

User62323503 posted
You can use Lead/Lag functions to access data from previous/next rows.
Refer below post
http://www.itdeveloperzone.com/2012/04/leadandlagfunctionsinsqlserver.html
Sunday, December 10, 2017 9:26 AM 
User991499041 posted
Hi Sadeq.hatami,
Use
sum(ab)
, not(a b+ lag(a b, 1, a b)
declare @tb table(artid int,num int,A decimal,B decimal) insert into @tb values (46,1,417636000,0), (47,1,15024000,0), (102,1,3418105650 ,0), (226,1,1160601286,0), (60,668,260000,0), (69,668,5500000,0) select *,sum(AB) Over(Order by num,artid) as newColumnValue from @tb
Screenshot
Regards,
zxj
 Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Wednesday, December 13, 2017 7:31 AM 
User1575600908 posted
thank's for your answer, can you explain me
sum(AB) Over(Order by num,artid)
what exactly does it do? i ca'nt understand it
thank you
Wednesday, December 13, 2017 7:52 AM 
User991499041 posted
Hi Sadeq.hatami,
thank's for your answer, can you explain me
sum(AB) Over(Order by num,artid)
what exactly does it do? i ca'nt understand it
Add an order by to the Over() and it will do a cumulative sum of the previous rows.
It means
newColumnValue
column is a cumulative sum of the (AB) for the previous demand orders.Cumulative sum of previous rows
http://www.sqlservercentral.com/articles/cumulative/109158/
Calculate cumulative sum of previous rows in SQL Server
http://sqlindia.com/calculatecumulativesumofpreviousrowssqlserver/
Regards,
zxj
 Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Wednesday, December 13, 2017 8:38 AM