I have sample data like below.
entity account amount id ac 1394 156.00 3453 ac 2512 100.00 3453 ac 2512 -70.00 3453 ac 1315 -156.00 3453 ac 1315 156.00 3453
I need to show the total sum from amount column irrespective of account column and need to get account which has maximum value in the amount column.
I used the below script for the same. I am able to get the account which has maximum value in the amount column but total sum is not correct. The total sum should be 186 in this case.
select a.account, a.total from ( select account,sum(amount) as total, rank() over (partition by id order by sum(amount) desc) as rank from test1 group by account,id) a where rank=1
the output of above query is
account total 1394 156.00
Advertisement
Answer
Given the requirements, you could use a window function to get the total sum() without partitioning, and then apply a clause with a row_number()
function order by amount desc
:
with windows as ( select account, sum(amount) over () as total_sum_amount, row_number() over (order by amount desc) as rn from test1 ) select account, total_sum_amount from windows where rn = 1;
Output:
account total 1394 186.00