Multiple row Subtraction form a single table in SQL

Jun 22 2018 1:08 AM
    
    
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.... 
 
 id lookupId size rate buySell
 1 110  10.5 1
 2 110 10.5 0
 3 1 50 11 1
 
 
 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 > 
 
 lookupId  buySell  TotalSize  TotalPrice
 1 0 10 105
 1 1 60 655
 
 But i want Like this output: form a single Quary (Subtraction should be always 1-0 )
 
lookupId Current SizeTotal AmountAvg 
1 50 550 11

 Thank you in advance...

Answers (4)