Deducing shares from SQL rows until all sold shares are exhausted PSET7 FINANCE

Question

The way I implemented my table, I registered all transactions to facilitate index and history. In index, I show only my positive stocks and they can be repeated if bought more than once (We also have to savet the price of each transaction). I sum the shares only to calculate and show the totals.

But, when I make sales I would have to zero each row of a specific stock until I exhaust the amount of sold shares. Any hints on how to make this UPDATE within SQL?

P.S. Before I sell, I check to see the amount of wanted shares to sell are less than the total available shares of that given stock.

Answer 1

It sounds like you are keeping multiple rows for multiple purchases of the same user and stock symbol combinations in the portfolio table. This requires searching all of those rows and subtracting from them for each sale.

Instead, why not keep one row for each userid/symbol combination and update the quantity as more shares are purchased or sold? This is easily implemented by first creating a primary or unique key based on the combination of those two columns. Then, when adding rows for purchases, use an ON DUPLICATE UPDATE clause.

Remember, the history table tracks the individual transactions, so they will be recorded there. The index table can keep track of the running totals.

If this answers your question, please click on the check mark to accept. Let's keep up on forum maintenance. ;-)