Multiple row Subtraction form a single table in SQL

Anilananda Chakrabortty

8y807SQL

Hi, can any one help me for this sql table....

i want to wrire a store procedure where i will pass lookupId (FK) based on lookup id it will give the requird output...

here the description....

This is my original table .... i have written a quary to get the total based on lookupId and BuySell

(1 = Buy and 0 = Sell)

select lookupId,buySell,

sum(size) as TotalSize ,

sum(size*rate) as TotalPrice

from tblLookupDetails where lookupId=1 group by lookupId,buySell

Its gives me this output >

But i want Like this output: form a single Quary (Subtraction should be always 1-0 )

lookupId	Current Size	Total Amount	Avg
1	50	550	11

Thank you in advance...

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke8y ago
Accepted answer
Try this:
declare @tblLookupDetails table
(
   [id] integer identity(1, 1) not null primary key clustered
  ,[lookupId] integer not null
  ,[size]  integer not null
  ,[rate] decimal(8, 2) not null
  ,[buySell] bit not null
)

insert into @tblLookupDetails (lookupId, size, rate, buySell) values
(1, 10, 10.5,1)
,(1, 10, 10.5,0)
,(1, 50, 11 ,1)

select lookupId,buySell,
sum(size) as TotalSize ,
sum(size*rate) as TotalPrice
from @tblLookupDetails where lookupId=1 group by lookupId,buySell

select [lookupId]
  ,sum(case when [buySell] = 1 then [Size] else -[Size] end) [Current Size]
  ,sum(case when [buySell] = 1 then [Size] * [rate] else -[Size] * [rate] end) / sum(case when [buySell] = 1 then [Size] else -[Size] end) [Average]
from @tblLookupDetails
where lookupId = 1
group by lookupId
Anilananda Chakrabortty8y ago
OMG so quick solution it is working ....
Thank you so much
Nitin Sontakke
Anilananda Chakrabortty8y ago
Thanks Sanghamitra Mohanty
I will check that links....
Sanghamitra Mohanty8y ago
Hi,

Could you please follow the example , you will got the answer:-
https://stackoverflow.com/questions/21394286/subtract-value-to-multiple-rows-sql

and another is :-
https://forums.asp.net/t/1501159.aspx?SQL+Server+Subtract+Column+in+Two+Rows+in+Same+Table

Thanks
Sanghamitra