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

  • 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!

  • Do you have any data?

  • 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.

  • 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

  • At least it's Friday.

    Enjoy the coffee and glad you figured it out.

  • >> 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. 

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

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