Take the 2-minute tour ×

I am writing a simple application in java and I am stuck in one sql statement. Lets say that I have two tables called items with attributes itemId and quantity and orderDetails also with attributes itemId and quantity. In the table Items I keep all the items and in orderDetails table I keep an itemId and quantity which was hired out to the customers. The goal is to write a sql statement which returns table with itemId and quantity of items which are currently available. I tried it several ways but I did not find a proper solution. (I am very very new in sql). This is one of them which I tried:

SELECT itemId, quantity - (SELECT SUM(quantity) FROM orderdetails GROUP BY itemId)
FROM items, orderdetails 
WHERE orderdetails.itemid = items.itemid;

Thanks in advance!

share|improve this question
@Egor why editing so eagerly?.. –  Yasir Arsanukaev Apr 26 '13 at 12:12
@YasirArsanukaev - Just formatted. –  Egor Skriptunoff Apr 26 '13 at 12:14
add comment

2 Answers

up vote 2 down vote accepted

I think that you could use correlated query (which IIRC should generally be avoided):

   items.quantity - 
       (select sum(quantity) 
          from orderdetails 
          where orderdetails.itemid = items.itemid group by itemid) left
from items;

Or you could join two tables:

select items.itemid, quantity - total.q left from
  (select itemid, sum(quantity) q from
   group by itemid) total
where items.itemid = total.itemid (+);
share|improve this answer
Forgot to handle NULL. Just call NVL in the second subtraction operand. –  Yasir Arsanukaev Apr 26 '13 at 12:54
add comment

You must provide some table data, for example:

select items.itemId, items.quantity - sum(quantity)
from items, orderdetails 
where orderdetails.itemid = items.itemid
group by items.itemId, items.quantity
share|improve this answer
add comment

Your Answer


