CODEWITHSUNDEEP
sqloracle-databaseoracle12c
Supun Amarasinghe
Supun Amarasinghe

Reputation: 1443

Join tables based on Aggregate Function result

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

Upvotes: 3

Views: 644

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can do this in a single query without a subquery:

select c.name, c.address, c.customerid, sum(o.orderamount)
from customers c join
     orders o
     on o.customerid = c.customerid
group by c.name, c.address, c.customerid
having sum(o.orderamount) > 50000;

having is like where, except it filters after aggregation. You can use aggregation functions in the having clause.

Upvotes: 4

Related Questions