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:

table_2:

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).

CodePudding user response：

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:

Check the demo here.