TSQL Add two Numbers
Solution 1
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
Solution 2
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
Frater
Updated on November 26, 2022Comments
-
Frater 12 months
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
-
juergen d over 9 yearsDO both columns have a number data type`
-
Frater over 9 yearsyeup, just numbers. And I get like a concatenation of them 90100.
-
juergen d over 9 yearsShould work: sqlfiddle.com/#!3/df6a8/1
-
JC Ford over 9 yearsHe'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
-
user2989408 over 9 yearsIf the result is the concatenation, then the cols are not numeric data types.
-
Frater over 9 yearsThey are both nvarchar. Should I change them?
-
juergen d over 9 yearsEhm, YES! If it only contains numbers without decimal places you can use
INT
-
Frater over 9 yearsThanks, Ill drop and recreate the table with those as int.
-
MatBailie over 9 yearsI 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?
-
paparazzo over 9 yearsAs long as you are recreating the table you could add a computed column for TotalCounted
-
Tomasito over 9 yearsBTW. IMHO. Column name with
space
is asking for trouble. -
Mike over 9 yearsIt doesn't sound like your stuck with the existing table, but you could also code around it. stackoverflow.com/questions/2000045/…
-