MySQL sum with negative decimals

Question

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)

your screenshots are restricted. please edit the question and post the sample data and table structure. — Taryn, Sep 28 '12 at 20:22
@bluefeet - i updated the links to the images, should work now. — drschultz, Sep 28 '12 at 20:26

score 2 · Accepted Answer · answered Sep 28 '12 at 20:34

2

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)

answered Sep 28 '12 at 20:34

Taryn

242,637
56
362
405

Note that casting to `DECIMAL` without specifying precision will produce integer values only. – lanzz Sep 28 '12 at 20:35
@bluefeet thanks, i hear what you're saying about storing it as a string, but this was before my time on this project. – drschultz Sep 28 '12 at 20:41

MySQL sum with negative decimals

1 Answers1