How to use select statement inside SUM function

Question

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?

Gordon Linoff · Accepted Answer · 2017-05-07 11:41:18Z

3

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;

answered May 7, 2017 at 11:41

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jim Macaulay · Accepted Answer · 2017-05-07 09:18:49Z

2

You can use below query

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

Order by clause is used to sort with respect to ID

answered May 7, 2017 at 9:18

Jim Macaulay

5,2515 gold badges32 silver badges60 bronze badges

1 Comment

Bilal Over a year ago

Column 'A.Block' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Rahul · Accepted Answer · 2017-05-07 09:49:16Z

2

I think you just want to SUM() group by block like

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

edited May 7, 2017 at 9:49

answered May 7, 2017 at 9:16

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

3 Comments

Bilal Over a year ago

It's working fine but the problem is still the same. Its giving me sum of 2500 but I want it to give me 1500 as GF-2 has 2 entries and in second entry its balance became 0. I want it to include latest entry in in sum function.

Bilal Over a year ago

Incorrect syntax near ')'. Sorry m not very good at SQL to fix that syntax myself. The error is at 2nd Sum's finishing bracket

Bilal Over a year ago

still its adding that 1000.

Fandango68 · Accepted Answer · 2019-08-12 04:27:02Z

2

You can use a SUM(DISTINCT ...)

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

answered Aug 12, 2019 at 4:27

Fandango68

4,9555 gold badges50 silver badges88 bronze badges

Collectives™ on Stack Overflow

How to use select statement inside SUM function

4 Answers 4

Comments

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related