SQL that returns the total products quantity for orders with a status of delivered (different two tables)

Question

I want to get the total products quantity for orders with a status of delivered. How can I write it as a single sql query?

table_1:

id	order_id	product_id	quantity
1	100001	123456780	3
2	100002	123456781	1
3	100002	123456782	5
4	100003	123456783	2

table_2:

id	order_id	order_status	order_date
1	100001	preparing	2023-01-26
2	100001	prepared	2023-01-26
3	100001	delivered	2023-01-26
4	100002	preparing	2023-01-26
5	100002	prepared	2023-01-26
6	100002	delivered	2023-01-26
7	100003	preparing	2023-01-26
8	100004	preparing	2023-01-26
9	100001	returned	2023-01-27

The sql query below didn't work as expected. Because it doesn't look at the latest status of the order:

SELECT SUM(t1.quantity) AS total
  FROM table_1 t1
  LEFT JOIN table_2 t2
    ON t1.order_id = t2.order_id
 WHERE t2.order_status = 'delivered'

Result: 9 (3+1+5) This result is not correct. Because the last status of the order number 100001 was Returned, it should not have added the number 3 to the total. Correct result should be 6 (1+5).

Answer 1

There are two issues in your query:

• You're not taking the latest record for each order_id. You can do such thing using the ROW_NUMBER window function, that assigns a ranking to each record of order_id ordered by order_date descendently (last order_date for each order_id has rownum = 1). Then you can filter on the rownum = 1.
• You're using a LEFT JOIN, but you want only rows that can be found in "table_2" and that strictly satisfy the conditions. You should rather use the INNER JOIN.

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY order_id 
                                ORDER     BY order_date DESC, id DESC) AS rn
    FROM table_2
)
SELECT SUM(quantity)
FROM      table_1 t1
INNER JOIN cte     t2
        ON t1.order_id = t2.order_id
       AND t2.order_status = 'delivered'
       AND t2.rn = 1

---

If you can't use common table expressions for some reason, an equivalent way of dealing with it is turning the cte into a subquery:

SELECT SUM(quantity) 
FROM       table_1 t1 
INNER JOIN (SELECT *, ROW_NUMBER() OVER(PARTITION BY order_id 
                                        ORDER BY date DESC, id DESC) AS rn 
            FROM table_2) t2 
        ON t1.order_id = t2.order_id 
       AND t2.order_status = 'delivered' 
       AND t2.rn = 1

Output:

SUM(quantity)
6

Check the demo here.