get max value based on rank fucntion-CodePudding

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

CodePudding user response：

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