TSQL Add two Numbers

Question

I know this may be simple to some but Im having a hard time with this. I simply want to add two columns together. My first number is "FirstShift" = 90, "SecondShift" = 100. Why am I not getting 190? I should see my 23 records all with different values 190 being my top record.

SELECT
    [FirstShift] + [SecondShift] AS [Total Counted]
FROM

Thanks

yeup, just numbers. And I get like a concatenation of them 90100. — Frater, Mar 18 '14 at 19:06
He's not asking about the data in the columns but the column definitions. It sounds like the columns are a string type. Check with `exec sp_columns TableName` — JC Ford, Mar 18 '14 at 19:11
If the result is the concatenation, then the cols are not numeric data types. — user2989408, Mar 18 '14 at 19:12
Ehm, YES! If it only contains numbers without decimal places you can use `INT` — juergen d, Mar 18 '14 at 19:15
I put sugar in my pepper pot, and now my pepper pot doesn't work properly. Should I put my sugar in a sugar bowl instead? — MatBailie, Mar 18 '14 at 19:19
As long as you are recreating the table you could add a computed column for TotalCounted — paparazzo, Mar 18 '14 at 19:24
It doesn't sound like your stuck with the existing table, but you could also code around it. http://stackoverflow.com/questions/2000045/tsql-cast-string-to-integer-or-return-default-value — Mike, Mar 18 '14 at 19:31

score 5 · Accepted Answer · answered Mar 18 '14 at 19:50

If those aren't numeric fields, you are going to have to fix your schema, or cast them as numeric fields.

If you don't want to fix your schema, try changing your query to this.

SELECT
    CAST([FirstShift] AS DECIMAL) + CAST([SecondShift] AS DECIMAL) AS [Total Counted]
FROM

score 3 · Answer 2 · answered Mar 18 '14 at 19:19

Ok... "really" Do both the table columns representing [FirstShift] and [SecondShift] have a numeric datatype?

I know its been asked but did you really mean what you said?

The "+" operator will act as a "concatenator" if either of the fields is a string. If both fields are numeric it will act as a "sum" operation...

Table [A]
[FirstShift] VARCHAR(10)    holds value "90"
[SecondShift] VARCHAR(10)   holds value "100"

[FirstShift] + [SecondShift] ==>  "90100"

WHEREAS

Table [A]
[FirstShift] INTEGER(10)    holds value 90
[SecondShift] INTEGER(10)   holds value 100

[FirstShift] + [SecondShift] ==>  190

TSQL Add two Numbers

2 Answers2