Compare sum of two select results

Question

Using Firebird, I have two tables, Orders and DespatchedParts.

Orders table:

ID  CustomerID  Total_number_parts
--  ----------  ------------------
1   1           5
2   1           4
3   2           10
4   1           5
5   3           20

DespatchedParts table:

ID  OrdersID  PartQty
--  --------  -------
1   1         2
2   1         3
3   2         10
4   3         10
5   3         10

So in the Orders table the sum of Total_number_parts for Cust 1's orders is 5+4+5=14.

Then looking in DespatchedParts, we want the sum of PartQty for Customer 1's, so 2+3=5.

So Customer 1 has ordered 14 parts and 5 have been dispatched. I want to get all the Customer ID's who have received all their parts.

Below is what I have got so far. I don't know where to put the sum(total_number_parts) = sum(qtyparts) part.

Select a.customerID, sum(a.Total_number_parts)
From Orders a
Join (Select sum(PartQty) from OrderParts Group by customerID) B
   on B.customerID = a.customerID
Group By a.CustomerID

Answer 1

You want to select all customers whose orders have been completely fulfilled. So first you need to know how many parts the customer has ordered in total:

select customerid, sum(total_number_parts) ordered
from orders
group by customerid

You also need to know how many parts have been shipped for each customer. You will need to join orders and dispatched parts to get the customerid associated with the dispatched parts.

select o.customerid, sum(dp.partqty) dispatched
from orders o 
left join dispatched_parts dp
   on dp.ordersid = o.id 
group by o.customerid

I'm using a left join here, so you also get the total quantity if no parts have been dispatched yet. This is not necessary for your question, but becomes relevant if you want to get all customers who haven't received all their parts yet.

We combine these two, and apply the requested condition in the where-clause:

select a.customerid
from (
    select customerid, sum(total_number_parts) ordered
    from orders
    group by customerid
) a
inner join (
    select o.customerid, sum(dp.partqty) dispatched
    from orders o 
    left join dispatched_parts dp
       on dp.ordersid = o.id 
    group by o.customerid
) b on b.customerid = a.customerid
where ordered = dispatched

Assuming the corrected orderids (1, 1, 3, 5, 5) I suggested in the comments, this yields

customerid
2
3

Alternatively, you could move the second part entirely into the where-clause by making it a correlated sub-query:

select a.customerid
from (
    select customerid, sum(total_number_parts) ordered
    from orders
    group by customerid
) a
where a.ordered = (
    select sum(dp.partqty) 
    from orders o 
    left join dispatched_parts dp
       on dp.ordersid = o.id 
    where o.customerid = a.customerid
)

Answer 2

Set up test data:

CREATE TABLE #order (id INT, customerid INT, total_number_parts INT)
INSERT INTO #order ( id ,customerid ,total_number_parts)VALUES  ( 1,1,5)
INSERT INTO #order ( id ,customerid ,total_number_parts)VALUES  ( 2,1,4)
INSERT INTO #order ( id ,customerid ,total_number_parts)VALUES  ( 3,2,10)
INSERT INTO #order ( id ,customerid ,total_number_parts)VALUES  ( 4,1,50)
INSERT INTO #order ( id ,customerid ,total_number_parts)VALUES  ( 5,3,20)

CREATE TABLE #despatchedparts (id INT, ordersid INT, partqty int)
INSERT INTO #despatchedparts (id, ordersid, partqty) VALUES (1,1,2)
INSERT INTO #despatchedparts (id, ordersid, partqty) VALUES (2,1,3)
INSERT INTO #despatchedparts (id, ordersid, partqty) VALUES (3,2,10)
INSERT INTO #despatchedparts (id, ordersid, partqty) VALUES (4,3,10)
INSERT INTO #despatchedparts (id, ordersid, partqty) VALUES (5,3,10)

Solution:

SELECT o.id AS [OrderId], o.customerid AS [CustomerId], o.total_number_parts AS [Parts orded], SUM(d.partqty) AS [Parts sent],
    CASE WHEN o.total_number_parts = SUM(d.partqty) THEN 'All delivered'
    WHEN SUM(d.partqty) = 0 THEN 'None delivered'
    ELSE 'Part delivered' END AS [Status]
FROM #order o
LEFT outer JOIN #despatchedparts d ON o.id = d.ordersid
GROUP BY o.id, o.customerid, o.total_number_parts

I only noticed this was for Firebird part way through so sorry if that doesn't work. You could also do:

SELECT o.*
FROM #order o
WHERE o.total_number_parts = (SELECT SUM(partqty) FROM #despatchedparts d WHERE o.id = d.ordersid)