Hi,
Here is the table in question.

drop table `portfolio1`;
CREATE TABLE `portfolio1` (
`ccode` varchar(99) default NULL,
`symbol` varchar(99) default NULL,
`transaction_date` date default NULL,
`rate` decimal(11,2) default NULL,
`quantity` int(11) default NULL,
`transaction` enum('buy','sell') default NULL
) TYPE=MyISAM;

INSERT INTO `portfolio1` VALUES ( 'soak', 'A', '2001-02-02', '345.92',
'50', 'buy');
INSERT INTO `portfolio1` VALUES ( 'soak', 'A', '2002-09-02', '345.92',
'100', 'buy');
INSERT INTO `portfolio1` VALUES ( 'soak', 'A', '2003-09-02', '345.92',
'150', 'buy');
INSERT INTO `portfolio1` VALUES ( 'soak', 'A', '2002-12-05', '500.00',
'100', 'sell');
INSERT INTO `portfolio1` VALUES ( 'soak', 'A', '2003-12-05', '900.00',
'175', 'sell');

INSERT INTO `portfolio1` VALUES ( 'soak', 'D', '2001-03-25', '39.90',
'500', 'buy');
INSERT INTO `portfolio1` VALUES ( 'soak', 'D', '2002-02-25', '39.90',
'100', 'buy');
INSERT INTO `portfolio1` VALUES ( 'soak', 'D', '2003-03-25', '39.90',
'300', 'buy');
INSERT INTO `portfolio1` VALUES ( 'soak', 'D', '2002-12-05', '67.00',
'200', 'sell');
INSERT INTO `portfolio1` VALUES ( 'soak', 'D', '2004-12-09', '87.00',
'200', 'sell');


query I know...

SELECT
sub1.ccode,
sub1.symbol,
SUM(sub1.quantity_buy) buy,
SUM(sub1.quantity_sell) sell,
(SUM(sub1.quantity_buy) - SUM(sub1.quantity_sell)) stock,
MAX(sub1.transaction_date) transaction_date
FROM
(
SELECT ccode, symbol, quantity quantity_buy, 0 quantity_sell, transaction_date
FROM `portfolio1` p
WHERE transaction = 'buy'
union all
SELECT ccode, symbol, 0, quantity quantity_sell, ''
FROM `portfolio1` p
WHERE transaction = 'sell'
) sub1
GROUP BY
sub1.ccode, sub1.symbol;


The results are as per my expectations.

ccode  symbol  buy  sell  stock  transaction_date  
soak A 300 275 25 2003-09-02 
soak D 900 400 500 2003-03-25 

But I do also want to know the corresponding quantity bought on that
particular transaction_date.

ccode |symbol | buy | sell | stock | transaction_date | bought
soak | A | 300 | 275 | 25 | 2003-09-02 | 150
soak | D | 900 | 400 | 500 | 2003-03-25 | 300


and if it is possible, the second last entry if the stock > bought like this...

ccode |symbol | buy | sell | stock | transaction_date | bought
soak | A | 300 | 275 | 25 | 2003-09-02 | 150
soak | D | 900 | 400 | 500 | 2003-03-25 | 300
soak | D | 900 | 400 | 500 | 2002-02-25 | 100
soak | D | 900 | 400 | 500 | 2001-03-25 | 100

Note: even if 500 quantity was bought on 2001-03-25 I want to display only 100
in the bought column above because 400 units have already been sold off.

I am using the latest version of mysql and oracle. 
So you can write a query that will work in either mysql or oracle (or
both. oracle prferred).

Hi, I just want to clarify something - I'm not completely sure what you're after.

The first part is simple enough, just details of the latest
transaction. (One point, though: do you want a negative quantity to
display if the last transaction was a sell?) The second part is
confusing me a little. I think I've worked it out, but I want to
confirm that before I go and try to code it.

I think you want details of every occasion you've bought stock that
you are currently holding, assuming for simplicity that when you sell
stock you sell on an oldest-first basis. In other words we assume that
the 500 of stock D being held after all those transactions comes from
the two most recent buys plus 100 of the oldest buy, and you want to
display data for all these.

e.g. suppose I made the following transactions:

2004-01-01 buy 250
2004-02-01 sell 150
2004-03-01 buy 50
2004-04-01 sell 125
2004-05-01 buy 75

you want want to display 2004-03-01 buy 25 and 2004-05-01 buy 75. Correct?

One point, though: do you want a negative quantity to display if the
last transaction was a sell?

No. Stock balance can not be negative.
_____
e.g. suppose I made the following transactions:
2004-01-01 buy 250
2004-02-01 sell 150
2004-03-01 buy 50
2004-04-01 sell 125
2004-05-01 buy 75

you want want to display 2004-03-01 buy 25 and 2004-05-01 buy 75. Correct?

Yes. That's correct. Just add two more dimensions i.e. client code and product code.

Hi oksoft,

I realise stock quantity can't be negative, but you said:
"But I do also want to know the corresponding quantity bought on that
particular transaction_date." 
The transaction date you're using is the date of the last transaction
on the stock, which could be either a buy or a sell. What do you want
to display if it was a sell?

Actually, there's another possibility I just thought of. It may be
that in fact you don't necessarily want the most recent transaction,
just the most recent buy. Is that correct? If so your current query is
incorrect in the case of the last transaction being a sell.

>> you don't necessarily want the most recent transaction, just the
most recent buy.
>> Is that correct? 
Yes.

>> If so your current query is incorrect in the case of the last
transaction being a sell.
In my test case, I am getting the correct latest transaction date of 'buy'. 
If you think the current query is wrong, 
a) Can you provide an example?
b) Can you correct it?

Sorry, my mistake on that point. I'd forgotten that you weren't
returning the date in Sub1 for a sell transaction, so (since the first
transaction for any stock has to be a buy) the date of the latest buy
will indeed always be returned. However, I believe that in your first
line of results (for stock 'A') the amount shown at the end should be
25, not 150, since you have already sold all but 25 of this stock. Can
you confirm this?

If that's correct, the following code should do the trick.

SELECT ccode, symbol, Sub1.buy, Sub1.sell, 
  Sub1.buy - Sub1.sell AS stock, transaction_date, 
  p1.quantity - Sub1.sell + least(Sub1.past_buys, Sub1.sell) AS bought
FROM portfolio1 AS p1 INNER JOIN 
  (SELECT ccode, symbol, p2.transaction_date, 
    sum(decode(p3.transaction, 'buy', p3.quantity, 0)) AS buy,
    sum(decode(p3.transaction, 'sell', p3.quantity, 0)) AS sell,
    sum(decode(p3.transaction, 'buy', decode(sign(p3.transaction_date
- p2.transaction_date), -1, p3.quantity, 0), 0)) AS past_buys
  FROM portfolio1 p2 INNER JOIN portfolio1 p3 USING (ccode, symbol)
  GROUP BY ccode, symbol, p2.transaction_date) Sub1 
USING (ccode, symbol, transaction_date)
WHERE p1.transaction = "buy" AND p1.quantity + Sub1.past_buys > Sub1.sell;

Let me know if you have any trouble - I am more used to Oracle SQL but
I could only test it in Access, so the translation of this from Access
SQL back to Oracle SQL hasn't been tested. I think I got everything,
but possibly there's something lurking.

Regards, manuka-ga