Stock Trading System Calculations via SQL

Question

Need to calculate a stock market portfolio based on trades in a database. The calculation I'm looking to do via SQL are average price, rate of return, portfolio value, etc.

Sample data:

Stock   Shares Price Value
A       100     50     5000
A       -20     60    -1200
A        50     40     2000

I haven't been able to do it with SQL, so I'm guess I may need to script the output, so any help there appreciated.

Answer 1

Note that the current price is hard coded (38) since it is not supplied, it would need to be supplied in another table which would be joined to the stocks table, but the principle is the same.

create table stocks (stock varchar2(10),shares number, price number);
insert into stocks values('A', 100, 50);
insert into stocks values('A', -20, 60);
insert into stocks values('A', 50, 40);

select stock, sum(shares) number_of_shares, round(sum(shares*price)/sum(shares),2) average_price,
    sum(shares*price) amount_paid,   sum(shares*38) value,   
    round((sum(shares*38)-sum(shares*price))/sum(shares*price)*100,2)||'%' rate_of_return
from stocks
group by stock

STOCK NUMBER_OF_SHARES AVERAGE_PRICE AMOUNT_PAID VALUE RETURN                    
A                  130         44.62        5800  4940 -14.83%