CREATE TABLE ACCOUNTS(ACC_ID,NAME) AS
(
SELECT 1,'ALICE' FROM DUAL UNION ALL
SELECT 2,'BOB' FROM DUAL UNION ALL
SELECT 3,'CHARLIE' FROM DUAL
)
CREATE TABLE TRANSACTIONS(TRAN_ID,ACC_ID,AMOUNT) AS
(
SELECT 1,1,7000 FROM DUAL UNION ALL
SELECT 2,1,10000 FROM DUAL UNION ALL
SELECT 3,1,-6000 FROM DUAL UNION ALL
SELECT 4,2,8000 FROM DUAL UNION ALL
SELECT 5,3,4000 FROM DUAL UNION ALL
SELECT 6,3,-1000 FROM DUAL UNION ALL
SELECT 7,3,9000 FROM DUAL UNION ALL
SELECT 8,3,5000 FROM DUAL
)
My effort :
SELECT
A.NAME AS NAME,
SUM(B.AMOUNT) TOT_AMOUNT
FROM ACCOUNTS A JOIN TRANSACTIONS B
ON A.ACC_ID = B.ACC_ID
GROUP BY A.ACC_ID
HAVING TOT_AMOUNT >10000;
Result table:
Name Balance
Alice 11000
Charlie 17000
ORA-00904: "TOT_AMOUNT": invalid identifier