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 | 1 | 10 | 10.5 | 1 |
| 2 | 1 | 10 | 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 Size | Total Amount | Avg |
| 1 | 50 | 550 | 11 |
Thank you in advance...