Stored Procedure:calculating Minus Dependent On Type With Group By

Hi i am trying to get the following result:


CALCULATE THE CURRENT NUMBER OF SHARES on base of number of shares bought and number of shares sold





so the logic is


int total


if transactiontype is "buy", total = total + amount bought


if transactiontype is sell, total = total-amount sold


plus must be grouped by share





base data are stored in 1 table as


share name transaction type share amount


tesco buy 200


tesco sell 50





sounds rather easy does it not?


well sql syntax must be very foreign to me as i do not manage it


I tried a few options:


1 to write a database function as above and then call it from sproc


but i need to use group by share name or id so it calculates for each share and it will not let me to use function in select with group by


2 i tried to use if within sproc but i get complain incorrect syntax (I use MSDE database accessed via Visual) - I think it suppose to be possible but it throws me out with the most simple if or = is there some special syntax?


3 I created 2 views: 1 view: all sold shares, 2nd view: all bought shares and their bought/sold quantities


as in


select share name, sum(sharequantity)


where transaction = sell


from table


group by share name


and the same for purchases


then I did union of these 2 views with boughtquantity - soldquantity what worked but it only showed me shares which were sold, if share was never sold it was not shown I guess due to union


so I am still stuck!