Thread: Newbie question re SQL

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)
-----          --------
 700           91,875

How do I get SQL to do this.  I tried using a CASE statement.  If
quantity > 0, then use the SELECT statement above.  If quantity < 0,
then find the average cost using all transactions from the date before
the date of the current transaction and then multiply that by the
negative quantity.  That didn't give me the right number.  Does anyone
have any suggestions?  Assume there will be multiple purchases and
sales for each security tracked by the database.

On Fri, Mar 01, 2002 at 02:53:20AM +0000,
  John <john@localhost.localdomain> wrote:
>
> How do I get SQL to do this.  I tried using a CASE statement.  If
> quantity > 0, then use the SELECT statement above.  If quantity < 0,
> then find the average cost using all transactions from the date before
> the date of the current transaction and then multiply that by the
> negative quantity.  That didn't give me the right number.  Does anyone
> have any suggestions?  Assume there will be multiple purchases and
> sales for each security tracked by the database.

If you have buy and sell transactions look the same except for sign
then you should be able to just "sum" things up.
You either want to calculate a price per share for each transaction
you do which is always positive (or perhaps easier with commisions,
to track the cost of the transaction as a signed number) and the number
shares transferred as a signed number.