I have two tables

    List item

    inventory_item_key(primary key)

The table example are provided here DB TABLES
I need query to find out those items for which (net_qty) i.e difference b/w sum of in_coming_qty & quantity & locked_qty is negative. arranged by node_key,rack_id, item_id,net_qty

Note: each distinct set {node_key,rack_id, item_id,net_qty} will have only 1 row in output. For ex :{node_key,rack_id, item_id} = {ABD101,RK-01,562879} has 4 rows in table_inventory but in output net_qty= -78(single row) .

The query I made is giving me result but can we do it in some other way?

SELECT l.node_key, 
       ( SUM(l.quantity + l.in_coming_qty) - SUM(l.locked_qty) ) AS net_qty 
FROM   table_inventory l, 
       table_item i 
WHERE  l.inventory_item_key = i.inventory_item_key 
GROUP  BY l.node_key, 
HAVING SUM(l.quantity + l.in_coming_qty) - SUM(l.locked_qty) < 0 
  • \$\begingroup\$ Which database engine are you using? MSSQL, Oracle, etc? \$\endgroup\$ Dec 28, 2011 at 13:25
  • \$\begingroup\$ @ChrisMarasti-Georg Oracle SQL Developer \$\endgroup\$ Dec 28, 2011 at 13:57

2 Answers 2


I am not exactly familiar with Oracle syntax, but I know that Oracle supports indexes on functions. I'm not sure if it will be used, since there is grouping involved, but you may wish to look into creating an index for this query if performance of the query becomes an issue. A guess at the syntax used to create the index would be:

Create index net_qty on table_inventory(SUM(quantity + in_coming_qty) - SUM(locked_qty));

You can search for "Oracle function based index" for more information.


I assume that we can additionally GROUP BY inventory_item_key without changing the result. If we can further assume that there are no (or few) rows in table_inventory without a matching row in table_item, then this query should be a bit faster:

SELECT l.node_key
FROM   table_item i
    SELECT inventory_item_key, node_key, rack_id 
          ,SUM(quantity + in_coming_qty - locked_qty) AS net_qty 
    FROM   table_inventory
    GROUP  BY inventory_item_key, node_key, rack_id
    HAVING SUM(quantity + in_coming_qty - locked_qty) AS net_qty < 0
    ) l USING (inventory_item_key)

Major points:

  • By aggregating and filtering the values table_inventory before the JOIN, fewer join-operations have to be made.

  • I simplified your quantity aggregation to SUM(quantity + in_coming_qty - locked_qty). the other possibility would be SUM(quantity) + SUM(in_coming_qty) - SUM(locked_qty). Either way makes more sense that the mixture you had. You would have to test which is fastest.

  • An explicit JOIN clause is preferable in SQL if applicable. In this case you can further simplify the syntax to USING (inventory_item_key).

The index @Chris proposes is impossible. Oracle has no way of knowing how to group these values. You need an index on table_item.inventory_item_key. Other than that I am not sure how to index aggregated values.

If speed is imperative and the values in table_inventory do not change very often, you could create a materialized view out of the sub-query l or the whole query. This would be a decisive speed-up.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.