Hi,
We have a table
CREATE TABLE USER_TRANSACTIONS(TRANSACTION_ID NUMBER PRIMARY KEY, TRANSACTION_AMOUNT NUMBER, USER_ID NUMBER, STATUS VARCHAR2(100));
INSERT INTO USER_TRANSACTIONS
VALUES(1, 151, 1001, 'SUBMITTED');
VALUES(2, 236.50, 1001, 'APPROVED');
VALUES(3, 4329.21, 1001, 'APPROVED');
VALUES(4, 991, 1002, 'SUBMITTED');
VALUES(5, 146.50, 1002, 'APPROVED');
VALUES(6, 6545.21, 1002, 'APPROVED');
VALUES(7, 4543.87, 1002, 'PENDING');
I need a output to display all the records of table, I need a new attribute APPROVED_VALUE in output >> if STATUS is APPROVED, I need to display SUM of TRANSACTION_AMOUNT of all transactions of USER_ID which are in APPROVED status.
TRANSACTION_ID AMOUNT USER_ID STATUS APPROVED_VALUE
1 151 1001 SUBMITTED 0
2 236.5 1001 APPROVED 4,565.71 => (sum of 2, 3)
3 4329.21 1001 APPROVED 4,565.71 => (sum of 2, 3)
4 991 1002 SUBMITTED 0
5 146.5 1002 APPROVED 6,691.71 => (sum of 5, 6)
6 6545.21 1002 APPROVED 6,691.71 => (sum of 5, 6)
7 4543.87 1002 PENDING 0
I want to get above output in best optimal way. (I have restrictions of in using sub query and inline views due to complexity of actual query).
I have tried with below statement, But I am not sure how to use filter STATUS = APPROVED in analytical function. Please help.
SELECT TRANSACTION_ID,TRANSACTION_AMOUNT,USER_ID,STATUS, CASE WHEN STATUS = 'APPROVED' THEN SUM(TRANSACTION_AMOUNT) OVER(PARTITION BY USER_ID WHERE STATUS = 'APPROVED' ) ELSE 0 END APPROVED_VALUE
FROM USER_TRANSACTIONS;