Oracle Join – How to Join Two Tables Based on Aggregate Function Sum() Result

aggregatejoin;oracleoracle-12c

I have a small issue related to joining two tables based on the result of SUM().Following are my tables.

Customers Table  

CUSTOMERID      NAME AGE   ADDRESS       PHONE
        1        AA  33    Some Address  123654789
        2        BB  35    Some Address  123654789
        3        CC  55    Some Address  987654789
        4        DD  55    Some Address  987654789
        5        EE  25    Some Address  365754789
        6        FF  20    Some Address  365754789


Orders Table 

    ORDERID  CUSTOMERID ORDERAMOUNT
    C12335    3         50000
    F12336    3         12000
    C12337    3         5000
    C12338    1         3700
    C12339    2         1100

I display the customerID,Name,Address,Sum of orders of the customers whose Sum of ORDERAMOUNT is more than 50000.

However,since I cannot use an Aggregate function like SUM() after a WHERE clause,I am in trouble here. 

SELECT C.NAME,C.ADDRESS,O.CUSTOMERID
FROM CUSTOMERS C
INNER JOIN
(
    SELECT CUSTOMERID,SUM(ORDERAMOUNT) AS sumorders FROM ORDERS GROUP BY CUSTOMERID
)O ON C.CUSTOMERID = O.CUSTOMERID;

The most progressive working query I could write was this and it does not evaluate the condition of Sum of ORDERAMOUNT is more than 50000.

Is there a way that I can evaluate that condition as well? A help is much appreciated. Thanks in advance 🙂

Best Answer

You cannot use SUM in a WHERE clause. However, you can use it in a HAVING

SELECT C.NAME,C.ADDRESS,O.CUSTOMERID
FROM CUSTOMERS C
INNER JOIN
(
    SELECT CUSTOMERID,SUM(ORDERAMOUNT) AS sumorders 
    FROM ORDERS 
    GROUP BY CUSTOMERID 
    HAVING SUM(ORDERAMOUNT)>5000
)O ON C.CUSTOMERID = O.CUSTOMERID;
Related Question