Re: Newbie question re SQL

On Fri, 01 Mar 2002 02:53:20 GMT, John <john(at)localhost(dot)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;