Very interesting problem

[iinfoque]

Hello everybody,

I have made a table having fields fruits, buysell, quantity, date and following data:

Fruits ---------------- Buysell ------------- Quantity ---------- Date
Orange ---------------- 1 ---------------- 1000 -------- 2003-12-01
Apple ------------------ 1 ---------------- 200 -------- 2003-12-01
Pineapple -------------- 1 ---------------- 700 -------- 2003-12-01
Apple ----------------- 1 ---------------- 500 -------- 2003-12-01
Orange ---------------- 2 ---------------- 1000 -------- 2003-12-01
Pinapple -------------- 2 ---------------- 500 -------- 2003-12-01

Here 1 is buy and 2 is sell, I want to display only those fruits which are not completely sold alongwith their leftover quantity i.e. Apple Remaining items 700 and Pinapple Remaining 200, I do not want Orange to be displayed as it is sold completely.

Please help me out.

[liquorvicar]

why don't you split the buysell into two columns like this:
Fruits ---------------- Buy -------------sell ---------- Date
Orange ---------------- 1000 ---------------- 1000 -------- 2003-12-01
Apple ------------------ 200 ---------------- 0 -------- 2003-12-01
Pineapple -------------- 700 ---------------- 500 -------- 2003-12-01
Apple ----------------- 500 ---------------- 0 -------- 2003-12-01

then you could just:
SELECT * FROM mytable WHERE buy>sell

otherwise it is possible, i think, like this:
SELECT f1.Fruits,(f1.Quantity-f2.Quantity) AS Qleft FROM mytable f1,mytable f2 WHERE f1.Fruits=f2.Fruits AND f1.Buysell=1 AND f2.Buysell=2 HAVING Qleft>0
although as is this won't pick up entries that have a Buy but not a Sell

[Lars_Berg]

select fruits,
   sum(case when buysell = 1 then sell else 0 end) -
   sum(case when buysell = 2 then sell else 0 end) as leftover
from t
group by fruits
having sum(case when buysell = 1 then sell else 0 end)
sum(case when buysell = 2 then sell else 0 end)