Re: Newbie question re SQL

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