TSQL Add two Numbers

16,377

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
Share:
16,377
Frater
Author by

Frater

Updated on November 26, 2022

Comments

  • Frater
    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
      juergen d over 9 years
      DO both columns have a number data type`
    • Frater
      Frater over 9 years
      yeup, just numbers. And I get like a concatenation of them 90100.
    • juergen d
      juergen d over 9 years
    • JC Ford
      JC Ford over 9 years
      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
    • user2989408
      user2989408 over 9 years
      If the result is the concatenation, then the cols are not numeric data types.
    • Frater
      Frater over 9 years
      They are both nvarchar. Should I change them?
    • juergen d
      juergen d over 9 years
      Ehm, YES! If it only contains numbers without decimal places you can use INT
    • Frater
      Frater over 9 years
      Thanks, Ill drop and recreate the table with those as int.
    • MatBailie
      MatBailie over 9 years
      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?
    • paparazzo
      paparazzo over 9 years
      As long as you are recreating the table you could add a computed column for TotalCounted
    • Tomasito
      Tomasito over 9 years
      BTW. IMHO. Column name with space is asking for trouble.
    • Mike
      Mike over 9 years
      It doesn't sound like your stuck with the existing table, but you could also code around it. stackoverflow.com/questions/2000045/…