PLSQL calculate per group

Question

I have a table which contains every order for all suppliers. I have the following query calculating the total quantity ordered for each supplier filtered on different date parameters.

SELECT 
    (
        SELECT SUM(ORDER_QTY)
        FROM  MY_TABLE
        WHERE  DELIVERY_DATE BETWEEN TRUNC(SYSDATE, 'YEAR') AND TRUNC(SYSDATE)
    ) AS YTD,
    (
        SELECT SUM(ORDERQTY)
        FROM   MY_TABLE
        WHERE  STATE = 'CONFIRMED'
        OR     STATE = 'RELEASED'
        AND    DELIVERY_DATE >= SYSDATE
    ) AS OVERDUE,
    SUPPLIER,
    SUPPLIER_NO 
FROM MY_TABLE 
GROUP  BY SUPPLIER    

The query is returning the sum for all suppliers (i.e. the same number in each line) instead of the individual sum for each supplier. I am new to PLSQL and can't figure out where I'm going wrong.

Answer 1

One problem with your code is that the subqueries do not correlate the the outer query (typically, there should be a filter on the supplier number). Also, there is a mix of AND and OR in one of the WHERE clause that probably does not do what you want.

Bottom line, based on your problem statement, I think you don't want subqueries, but conditional aggregation. Something like:

SELECT SUPPLIER_NO, SUPPLIER,
    SUM(
        CASE
            WHEN DELIVERY_DATE BETWEEN TRUNC(SYSDATE, 'YEAR') AND TRUNC(SYSDATE) 
            THEN ORDER_QTY 
            ELSE 0 
        END
    ) as YTD,
    SUM(
        CASE 
            WHEN STATE IN ('CONFIRMED', 'RELEASED') AND DELIVERY_DATE >= SYSDATE 
            THEN ORDER_QTY 
            ELSE 0 
        END
    ) as OVERDUE
FROM MY_TABLE
GROUP BY SUPPLIER_NO, SUPPLIER