sql server - adding two columns together from sub queries

Question

OK, here goes. This must be quite an easy thing to do, but all I am doing is ripping out my ever shrinking hair !!!!

I have an SQL query along the lines of:

Select A.ID,
      A.field
      (select vchr_Number from tbl_two B where B.int_ParentId = A.ID) as 'Number1',
      (select vchr_Number from tbl_three C where C.int_ParentId = A.ID) as 'Number2',
      (Number1 + Number2) as 'Number3' 
From   tbl_Something A

What I am trying to do is add Number1 and Numnber2 together. I do need to return all three values

As both values are strings if I just use the normal addition '+', it will just concatenate the two strings so if Number1 = 7 and number2 = 8 then Number3 would be 78, not 15. I have tried using the Cast command to convert the string into integers, not sure about syntax.

Maciej Los · Accepted Answer · 2015-01-14 19:42:13Z

1

You need to convert data to proper data type (in below example, i use INT):

SELECT T.*,  CONVERT(INT, T.Number1) + CONVERT(INT, T.Number2) as 'Number3' 
FROM (
    SELECT A.ID, A.field, 
        (select vchr_Number
         from tbl_two B
         where B.int_ParentId = A.ID) as 'Number1',
        (select vchr_Number
         from tbl_three C
         where C.int_ParentId = A.ID) as 'Number2',
    From tbl_Something A
) AS T

or

SELECT A.ID, A.field, 
    (select CONVERT(INT, vchr_Number)
     from tbl_two B
     where B.int_ParentId = A.ID) +
    (select CONVERT(INT, vchr_Number)
     from tbl_three C
     where C.int_ParentId = A.ID) as 'SumOfNumbers',
From tbl_Something A

Note: you need to store data as its original data type: numbers as numbers, text as text, date as date, etc. Never use text /char, (n)varchar/ to store numeric or date data type!

edited Jan 14, 2015 at 19:42

answered Jan 14, 2015 at 18:28

Maciej Los

8,6311 gold badge22 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

gilesrpa Over a year ago

the sql provided is only a small sample of the overall query. so this solution is not viable. The solution must allow for this coding. The actual sql is over 200 lines long as is used as part of a report

Maciej Los Over a year ago

The basic idea is to store data as its original data type. So, if you can change data type in your database is the best option to you. Then you'll never be forced to convert anything.

gilesrpa Over a year ago

Hmmm, not making myself heard at the moment. Can I not just add the following code: '(select vchr_Number from tbl_two B where B.int_ParentId = A.ID) + (select vchr_Number from tbl_three C where C.int_ParentId = A.ID) ' but convert the vchr_number on each to int's?

Maciej Los Over a year ago

@user1546400, you can convert it this way: (SELECT CONVERT(NUMERIC_DATA_TYPE, VARCHAR_NUMERIC) FROM ...) + (SELECT CONVERT(NUMERIC_DATA_TYPE, VARCHAR_NUMERIC) FROM ...)

gilesrpa Over a year ago

Brilliant work Maciej. Worked perfectly. knew it was easy fix and it was in the end.

Jeremy · Accepted Answer · 2015-01-14 18:27:18Z

0

Select  A.ID,
      A.field,(Number1 + Number2) as 'Number3' 
From
(
Select A.ID,
      A.field
      (select vchr_Number from tbl_two B where B.int_ParentId = A.ID) as 'Number1',
      (select vchr_Number from tbl_three C where C.int_ParentId = A.ID) as 'Number2',
      (Number1 + Number2) as 'Number3' 
From   tbl_Something A
) T1

answered Jan 14, 2015 at 18:27

Jeremy

4,8582 gold badges23 silver badges24 bronze badges

5 Comments

Maciej Los Over a year ago

Number1 and Number2 are varchar data type.

gilesrpa Over a year ago

the sql provided is only a small sample of the overall query. so this solution is not viable. The solution must allow for this coding as shown. The actual sql is over 200 lines long as is used as part of a report. So can I just add the two numbers as a sub query?

Jeremy Over a year ago

yup. it does virtually nothing to the execution plan, but allows you to reference calculated columns as a name.

gilesrpa Over a year ago

Hmmm, not making myself heard at the moment. Can I not just add the following code: '(select vchr_Number from tbl_two B where B.int_ParentId = A.ID) + (select vchr_Number from tbl_three C where C.int_ParentId = A.ID) ' but convert the vchr_number on each to int's?

Jeremy Over a year ago

If you try it does it work? You should be able to, but be aware that as a correlated sub query, performance can degrade significantly if you paste that everywhere.

Collectives™ on Stack Overflow

sql server - adding two columns together from sub queries

2 Answers 2

5 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related