2

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
  • 1
    Did you mean ordersid values 1, 1, 3, 5, 5 instead of 1, 1, 2, 3, 3 for the dispatched parts? Otherwise customer 1 has received more parts than ordered. – Mark Rotteveel Jan 12 '18 at 9:20
2

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
)
| improve this answer | |
0

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)
| improve this answer | |
  • 1
    You seem to be using SQL Server syntax. Most of it will work, but Firebird can't have tables prefixed with a #, and quoting of fields is done with a ", not with square brackets. – Mark Rotteveel Jan 12 '18 at 9:15
  • Your query doesn't address the question, which ask for the customerids that have had all their parts shipped. Your first query could yield multiple rows for a single order (as despatchedparts seems to contain multiple rows per order), and your second only yields the orders that have been fulfilled, but doesn't answer the question if the customer has received all their parts of all their orders. – Mark Rotteveel Jan 12 '18 at 9:37
  • Thanks for the replyies, i don't think the last solution is quite right as one customer can have many orders in the order table, so i need the sum(orders.total_number_parts) where the customer id is 1, and compare this to the sum(partqty) in Despatchedparts. Everything i try doesnt seem to work! – Simon Jan 12 '18 at 10:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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