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