I've a case where the data needs to be represented based on the events
I need to achieve the following output
Explanation: ABC is represented thrice here and you can see date being different,
Initial Balance is the first_value of the ABC and the date combination.
Opening Balance is as of 01/12/2018
Closing Balance is as of 31/12/2018
Tried this sql but its throwing me Non-aggregate columns must be part of the associated group.
What wrong am doing and any insights on how to achieve the expected output.
select
id||date as ID,
max(loanamt) over(partition by id,date order by strtdate) as Initial_balance
,max( case when strtdate = '2018-12-01' then balamt else 0 end ) over(partition by id,date order by strtdate ) as Opening_balance
,max(case when strtdate= '2018-12-31' then balamt else 0 end ) over(partition by id,date strtdate desc ) as Closing_balance
from tab
where id = 'ABC'
group by id;
Based on your data and narrative this should return the expected result:
select
id||date_col as ID
,loanamt as Initial_balance
,max(case when strtdate = DATE '2018-12-01' then balamt else 0 end) as Opening_balance
,max(case when strtdate = DATE '2018-12-31' then balamt else 0 end) as Closing_balance
from tab
where id = 'ABC'
group by 1,2;
Simple aggregation, no need for OVER.