How to use select statement inside SUM function

I have a table which looks like this.

ID      |    Block     |    Flats  |    Ammount  |    Balance  |
1       |      1       |      GF-1 |    1000     |      500    |
2       |      1       |      GF-2 |    1000     |      500    |
3       |      2       |      GF-1 |    1000     |      500    |
4       |      2       |      GF-2 |    1000     |      1000   |
5       |      2       |      GF-2 |    1000     |      0      |

I want to execute sum query on this. I have tried

Select distinct A.Block,(Select Sum(Ammount) from t1 where block = A.block),(select Sum(Balance) from t1 where block = A.block) from t1 A

This query is working fine but its summing balance to 2500 but as ID 4 & 5 are of same Flat so I want it to sum latest of balance which should be 1500. I have tried to put a select statement inside sum function but that doesn’t work . So how can I achieve this?

Answer

You can select the most recent id for each block/flats combo first (using row_number()) and then aggregate:

Select t1.Block, sum(amount)
from (select t1.*,
             row_number() over (partition by block, flats order by id desc) as seqnum
      from t1
     ) t1
where seqnum = 1
group by t1.Block;

Advertisement

Answer