CODEWITHSUNDEEP
sqlsqlitejoinsum
Kariem
Kariem

Reputation: 25

Subtract two SUM GROUP BY fields

I have two tables itemOrders and itemUsage.

itemOrders has two fields: item_number and qty_ordered itemUsage has two fields: item_number and qty_used

I'm trying to word my SQL query so that it sums up the quantity of each item number in both tables then subtracts the totals in itemUsage from itemOrders

I've come up with this so far: 

SELECT itemOrders.item_number
    ,(
        SELECT sum(qty_ordered)
        FROM itemOrders
        GROUP BY itemOrders.item_number
        ) - (
        SELECT sum(qty_used)
        FROM itemUsage
        GROUP BY itemUsage.item_number
        ) AS item_total
FROM itemOrders
INNER JOIN itemUsage 
        ON itemOrders.item_number = itemUsage.item_number
GROUP BY itemOrders.item_number

What happens here is that all fields come out to 0.

Example if item number "A" was showing a total quantity of 3 ordered across all instances of "A" in the itemOrders table, and only a total quantity used of 1 across all instances of "A" in the itemUsage table. The sql should show the number one in the item_total field next to 1 instance of "A" in the item_number field.

Upvotes: 1

Views: 4858

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

The problem is you are creating a CARTESIAN PRODUCT and repeting the values on the SUM just calculate each value separated and then LEFT JOIN both. In case no item are used COALESCE will convert NULL to 0

SELECT io_total.item_number,  
       order_total - COALESCE(used_total, 0) as item_total
FROM (SELECT io.item_number,  sum(io.qty_ordered) as order_total
      FROM itemOrders io
      GROUP BY io.item_number
     ) io_total
LEFT JOIN (SELECT iu.item_number, sum(iu.qty_used) as used_total 
           FROM itemUsage iu
           GROUP BY iu.item_number
          ) as iutotal
        ON io_total.item_number = iutotal.item_number

Upvotes: 1

firereaction
firereaction

Reputation: 23

Well it looks like you are making three queries, two separate ones, one for each sum, and one with an inner join that isn't being used. Try

Select itemOrders.item_number, sum(itemOrders.qty_ordered - itemUsage.qty_used) as item_total 
from itemOrders INNER JOIN itemUsage
On itemOrders.item_number = itemUsage.item_number
GROUP BY itemOrders.item_number

Upvotes: 0

Related Questions