Can you share your inputs on below;
WITH t1 AS
( SELECT 'SK' Name, 1234 ID, 'Detroit' City, 'A' Class, 1000 Expenses FROM DUAL
UNION ALL
SELECT 'SK' Name, 4567 ID, 'Detroit' City, 'B' Class, 1500 FROM DUAL
UNION ALL
SELECT 'SK' Name, 1234 ID, 'Detroit' City, 'A' Class, 1250 FROM DUAL
)
SELECT * FROM t1;
Scenario 1:
I want the output to have 1 row which is consolidated based on the name, num, showing 'ID' corresponding to class 'A', showing 'City' and sum of expenses.
WITH t1 AS
( SELECT 'SK' Name, 1234 ID, 9999 NUM, 'Detroit' City, 'A' Class, 1000 Expenses FROM DUAL
UNION ALL
SELECT 'SK' Name, 4567 ID, 9999, 'Detroit' City, 'B' Class, 1500 FROM DUAL
UNION ALL
SELECT 'SK' Name, 1234 ID,9999, 'Detroit' City, 'A' Class, 1250 FROM DUAL
)
SELECT MAX(Name), MAX(DECODE(class,'A',ID)) ID, MAX(NUM), MAX(City), SUM(expenses) FROM t1
GROUP BY Name, City, num;
Expected output
NAME ID NUM CITY EXPENSES
SK 1234 9999 Detroit 3750
Can we optimize above query.?
Scenario 2:
However if there are records corresponding only to class 'B'
I want the output to have 1 row which is consolidated based on the name, num, showing 'ID' corresponding to class 'B', showing 'City' and sum of expenses.
WITH t1 AS
(
SELECT 'SK' Name, 4567 ID, 9999 Num, 'Detroit' City, 'B' Class, 1500 expenses FROM DUAL
)
SELECT MAX(Name), MAX(DECODE(class,'A',ID)) ID, MAX(NUM), MAX(City), SUM(expenses) FROM t1
GROUP BY Name, City, num;
Expected output
NAME ID NUM CITY EXPENSES
SK 4567 9999 Detroit 1500
Thanks for your time.