# SQL query for adding column value to compare with other column

I have two tables

table_inventory
List item
inventory_rack_key(primarykey)
node_key
rack_id
inventory_item_key
in_coming_qty,locked_qty
quantity

table_item
inventory_item_key(primary key)
item_id,product_zone


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,
l.rack_id,
i.item_id,
( 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,
l.rack_id,
i.item_id
HAVING SUM(l.quantity + l.in_coming_qty) - SUM(l.locked_qty) < 0

• Which database engine are you using? MSSQL, Oracle, etc? Dec 28 '11 at 13:25
• @ChrisMarasti-Georg Oracle SQL Developer Dec 28 '11 at 13:57

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));


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
,l.rack_id
,i.item_id
,net_qty
FROM   table_item i
JOIN  (
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.