如何在SUM函数中使用select语句

Question

我有张像这样的桌子。

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      |

我想对此执行sum查询。我试过了

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

这个查询很好，但是它的求和余额是2500，但是由于ID4和5是相同的，所以我希望它能把最新的余额之和，也就是1500。我尝试将select语句放入sum函数中，但这是行不通的。那我怎么能做到这一点呢？

Answer 1

您可以首先为每个block/flats组合选择最新的flats(使用row_number())，然后聚合：

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;

Answer 2

我想你只是想让SUM() group by block喜欢

select Block, sum(amount) totalamount,
sum(case when balance <> 0 then balance end) totalbalance
from t1
group by Block;

Answer 3

您可以使用下面的查询

Select A.Block, Sum(A.Ammount), Sum(A.Balance)
from t1 A group by A.Flats order by A.ID;

Order子句用于对ID进行排序