Search code examples
mysqlsumnegative-numberdecimal

MySQL sum with negative decimals

I have a qty column in my table, and i'm trying to sum the values. The field type is varchar(20). The sum of qty below should add up to exactly 0. It's a mix of negative and positive decimals.

exhibit A (screenshot)

When i perform the sum below, i'm getting a tonne of decimals instead of 0. I'm assuming this is a datatype issue. What's the best way to work around this?

exhibit B (screenshot)

Solution

  • You should not be storing numeric data as a string but if you do, then you will need to cast() it to apply a SUM() aggregate to it:

    SELECT SUM(CAST(yourcolumn AS DECIMAL(10, 2))) 
FROM yourtable

    So your query will be:

    select sum(cast(qty as DECIMAL(10, 2)))
from inventory i
where i.refDocNum = 485
  and i.refApp = 'WO'
  and i.type in (20, 21)