Re: Newbie question re SQL

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;