Sum Group By

Evan0211 — Tue, 15 Mar 2022 21:08:36 GMT

I have a table like this

Amount 1	Amount 2	ID
9500	0	123
9500	0	123
9000	0	456
0	1000	678
0	1000	678

If Amount 1 is not null, I need to get a summation of a group by the ID.

I am trying to do something like this:

=Sum(

If( Is NotNull(Amount1), aggr(sum(Num(Amount1, '$#,##0.00')
), [ID]),

If( Is NotNull(Amount2), aggr(sum(Num(Amount2, '$#,##0.00')
), [ID])

)

but it is giving me incorrect data returned. Any suggestions?

The result set I want is 123 to sum to 9500, 456 to sum to 9000 and 678 to sum to 1000. Note, I have tried using a distinct and it did not seem to impact the results as desired.

Re: Sum Group By

Or — Tue, 15 Mar 2022 21:36:00 GMT

It doesn't seem like you're looking for a sum - you seem to be describing picking a single value rather than summing them.

Given there only ever seems to be a value in one field or the other, you could just use

Sum(Amount1) + Sum(Amount2)

if you wanted to actually sum. However, since I don't think you're summing, perhaps:

alt(Only(Amount1),0) + Alt(Only(Amount2),0)

topic Sum Group By in App Development

Sum Group By

Re: Sum Group By