CODEWITHSUNDEEP
sqljoincompare
2FaceMan
2FaceMan

Reputation: 483

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

Upvotes: 1

Views: 2425

Answers (1)

user359040
user359040

Reputation:

Not really. There is this minor variant:

select v.* from (
SELECT l.node_key, 
       l.rack_id, 
       i.item_id, 
       SUM(l.quantity + l.in_coming_qty - 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
) v
where net_qty < 0

- which means that the SUM calculation only needs to be coded once, but you do still need to do a SUM.

Upvotes: 4

Related Questions