settling the table issues!

[Ahmed]

Hi there,
i have a table like this:

name: name of a stock
operation: can be 'b' or 's' (buy or sell)
quant: nuber of stocks to negotiate
user: the user
date: date of proposal
price: price of the stock


I want the query to output this:

name
buy: max price of buyers
sell: min price of sellers
quantbuy: quantity of stocks to negotiate
quantsell: quantity of stocks to negotiate

I've been trying with this:

SELECT tsell.name AS 'name', MAX( tbuy.price ) AS 'buy',
MIN( tsell.price ) AS 'sell', tbuy.quant AS 'quantbuy',
tsell.quant AS 'quantsell'
FROM proposals AS tsell, proposals AS tbuy
WHERE tsell.operation = 's'
AND tbuy.operation = 'b'
AND tsell.name = tbuy.name
GROUP BY tsell.name

But the quantity is not the one with that price, instead it is the first -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --

[Beau]

Try this:

SELECT name,
max(if(operation='b',price,null)) AS buy,
min(if(operation='s',price,null)) AS sell,
sum(if(operation='b',quant,0)) AS quantbuy,
sum(if(operation='s',quant,0)) AS quantsell
FROM yourtable
GROUP BY name; -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --