Subtract and Sum in SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Subtract and Sum in SQL

Author

Topic

Nil35
Starting Member

20 Posts

Posted - 2013-07-11 : 10:31:40

declare @a float
set @a = 100
select @a

create table #t
(id int,
colA int,
colB INT
)
insert into #t
select 1,2,4
union
select 2,4,3
union
select 3,3,2
union
select 4,5,4
union
select 5,8,1

SELECT * FROM #t

--need to create new column,
--in that in first row veriable @a - colA + ColB,
--starting from second row LAST ROW OF NEW COLUMN - colA + ColB

--second row example (fitst row of new column - colA + colB)
--third row example (second row of new comumn - colA + colB)

nil

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-11 : 11:08:30

See the following. However, if you have a lot of rows, it is going to perform poorly. If you are on SQL 2012, there are more efficient ways:

SELECT
	t1.*,
	@a + t3.ColC AS ColC
FROM
	#t t1
	OUTER APPLY
	(
		SELECT SUM(-ColA + Colb) AS ColC
		FROM #t t2
		WHERE t2.id <= t1.ID
	) t3

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-11 : 13:15:21

this is another way known as quirky update



declare @a float
set @a = 100

DECLARE @ID int,@A1 int, @B1 int



CREATE CLUSTERED INDEX IDX_ID ON #t(ID)
 

SELECT TOP 1 @ID = ID,
@A1 = A,
@B1 = B
FROM #t
ORDER BY ID

UPDATE #t
SET @A =C= @A - A + B,
@ID=ID

OPTION ( MAXDOP 1)

SELECT * FROM #t

http://visakhm.blogspot.in/2010/03/using-quirky-updates-to-develop-well.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-11 : 13:21:39

Quirky update is an undocumented feature, but as far as I can tell, it works every time without any problems (if you follow the rules). And, it is blazing fast.

Jeff Moden's article here has the rules aka ten-commandmends of quirky update http://www.sqlservercentral.com/articles/T-SQL/68467/ (look for the section Rules).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources