Having sum(amount) = 0 returns negatives as well.

Question

Post reply

Having sum(amount) = 0 returns negatives as well.

C-M-A

SSC-Addicted

Points: 407
More actions
July 30, 2021 at 1:51 pm

#3915453
Hello all,
I'm trying to get a list of ID's that have a total sum of their amount column = to zero... But I'm getting a lot of negative numbers, but no positives. So, for example, I might get an ID that has two rows in total which if you add up their amounts it equals -3.50. Why would that happen? I need to see those ID's whose amount column equals exactly 0.
```
select id FROM table
GROUP BY id
HAVING sum(Amount) = 0
```
Thanks all!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ZZartin SSC-Dedicated Points: 30896 More actions · Answer 1

ZZartin

SSC-Dedicated

Points: 30896

July 30, 2021 at 2:15 pm

#3915464

Do you have any data?

Steve Jones - SSC Editor SSC Guru Points: 734522 More actions · Answer 2

Likely you are missing some data here. If you run this, what do you get?

SELECT id, SUM(n.amount) FROM dbo.negsum AS n
GROUP BY id

If you have the ID that you think has -3.5, then can you select all those rows for that id and see what comes back?

Remember there is no ordering of rows, so if you need to check all IDs, you need to ensure an ORDER BY is used to help you manually check data.

C-M-A SSC-Addicted Points: 407 More actions · Answer 3

Oh I made a dumb mistake. I had something else in the where clause for the list of IDs that would indeed make particular accounts have a sum of zero, but when I would look at an individual account, the where clause wasn't the same so it would essentially show me everything with a negative sum. Basically, I needed more coffee.

Thanks everyone

Steve Jones - SSC Editor SSC Guru Points: 734522 More actions · Answer 4

At least it's Friday.

Enjoy the coffee and glad you figured it out.

jcelko212 32090 SSCrazy Eights Points: 9295 More actions · Answer 5

>> I'm trying to get a list of ID's that have a total sum of their amount column = to zero... But I'm getting a lot of negative numbers, but no positives. So, for example, I might get an ID that has two rows in total which if you add up their amounts it equals -3.50. Why would that happen? I need to see those ID's whose amount column equals exactly 0. <<

No data. No DDL. No meaningful data element names. This is generally considered pretty rude for the past 30 something years of SQL forums. Are you familiar with the signum function? It returns +1 for positive numbers, 0 for zero, and -1 for negative numbers. Most programming languages compile this function to one or two assembly language instructions, so it's pretty fast.

SELECT generic_id

FROM Someplace

GROUP BY generic_id

HAVING SIGN (SUM (vague_amount)) = 0;

Please post DDL and follow ANSI/ISO standards when asking for help.