Обсуждение: 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;

>>> Quan     Price       Cost
>>>====     =====       ====
>>>  300     125       37,500
>>>  500     135       67,500
>>> (100)     110      (13,125)  <-- how do you get this number?
>
> When I sell a security, to calculate the cost of the remaining
> identical securities, I must subtract from my total cost before the
> sale the number of units sold * their cost per unit (not their fair
> market value at the time of the sale).
>

I was reading recently that it can be helpful to access the
database _only_ through functions. Basically, create an API
for accessing your data. So you might create functions:

buy(id, quan, price)
sell(id, quan, price)

which would deal with your accounting situation.

Maybe you could keep a table with the to-date cost which
gets updated each time you buy() or sell()

You might also look at sourceforge.net as I remember reading
about an accounting system written for sql databases.
(Someone may already have scratched this itch :)

On Sat, 2 Mar 2002 00:38:48 +0000 (UTC), Lee Harr
<missive@frontiernet.net> wrote:

>> Quan     Price       Cost
>>====     =====       ====
>>  300     125       37,500
>>  500     135       67,500
>> (100)     110      (13,125)  <-- how do you get this number?

When I sell a security, to calculate the cost of the remaining
identical securities, I must subtract from my total cost before the
sale the number of units sold * their cost per unit (not their fair
market value at the time of the sale).

> First, I would not keep the cost as a field in the table, you can
> always get that from quan * price, right?

Agreed.  Unless, of course, I can't figure out how to get SQL to
calculate cost automatically!

> SELECT sum(quan*price) FROM trans;

Except that this isn't right because, as discussed above, when I sell,
the price at which I sell is the fair market value of each of my units
and not my cost per unit.

Thanks for your response.

--
John