Hi,
Not been able to solve this one. I have a table that has an acct # (varchar2) and then a total (number) associated with it.
What I'm trying to do is to put a total for each account by org code, grouping account numbers by specific (self defined) groups and doing a manipulation on that (subtraction).
I know I can do select acct, sum (case when (account < 4000) then total end ) over (partition by orgcode) as myoutput and that would give me AAA 200, BBB 0 and CCC 0.
What I want to do is take that code where the account # is < 4000 "sum (case when (account < 4000) then total end ) over (partition by orgcode)" and then subtract the total for 4000 accounts. "sum (case when (account >= 4000) then total end ) over (partition by orgcode)"
Doing select account, total, sum (case when (account < 4000) then total end ) over (partition by orgcode) - sum (case when (account >= 4000) then total end ) over (partition by orgcode) as total_rev_minus_expenses gives me 0. For the below example I should have AAA 200 - 50 = 150, BBB would equal -200 and CCC would equal -200.
table1
orgcode account total
AAA 3100 100.00
AAA 3300 100.00
AAA 7000 50.00
BBB 4253 200.00
CCC 6000 200.00