Re: Newbie question re SQL

On Sat, 2 Mar 2002 00:38:48 +0000 (UTC), Lee Harr
<missive(at)frontiernet(dot)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