On Fri, 01 Mar 2002 02:53:20 GMT, John <john@localhost.localdomain> wrote:
> I have created a PostgreSQL database to keep track of my investments.
> I have created one table to track my purchases and sales of
> securities. One piece of information I need to be able to extract
> from the database is the cost of each security. Calculating the cost
> of each security if I have only purchased that security is easy:
>
> SELECT quantity,price,quantity*price AS cost
> FROM transactions;
>
> But what if I have bought *and* sold the security? Let's say I bought
> 300 iShares on December 15, 2001 at $125 per share, 500 iShares on
> January 1, 2002 at $135 per share, and then I sold 100 iShares on
> February 15, 2002 at $110 per share. I can calculate my cost by hand
> easily enough, and the result would like the following:
>
> Quan Price Cost
>==== ===== ====
> 300 125 37,500
> 500 135 67,500
> (100) 110 (13,125) <-- how do you get this number?
> ----- --------
> 700 91,875
>
First, I would not keep the cost as a field in the table, you can
always get that from quan * price, right? So:
CREATE TABLE trans (
quan int,
price int);
INSERT INTO trans VALUES (5, 100);
INSERT INTO trans VALUES (5, 100);
INSERT INTO trans VALUES (10, 80);
INSERT INTO trans VALUES (-15, 125);
SELECT quan*price FROM trans;
SELECT sum(quan*price) FROM trans;