SQL create variable over columns

Question

I have a query that is becoming repetitive with the summation of the same columns. What I would like to do is create a variable so that my code does not become unnecessarily cluttered. A trivial example:

SELECT 
    (a.col1 + a.col2 + a.col3 + a.col4 + a.col5)/2 AS half,
    (a.col1 + a.col2 + a.col3 + a.col4 + a.col5)/3 AS third,
    (a.col1 + a.col2 + a.col3 + a.col4 + a.col5)/4 AS fourth,
    (a.col1 + a.col2 + a.col3 + a.col4 + a.col5)*2 AS twice,
    b.sepCol
FROM [Table A] a
JOIN [Table B] b ON b.someCol = a.someCol

I would like to be able to remove the need to type the sum(col1...col5) into something like:

@myVar = (a.col1 + a.col2 + a.col3 + a.col4 + a.col5)
SELECT 
    @myVar/2 AS half,
    @myVar/3 AS third,
    @myVar/4 AS fourth,
    @myVar*2 AS twice,
    b.sepCol
FROM [Table A] a
JOIN [Table B] b ON b.someCol = a.someCol

Preferably I would like to keep this in the same query and not have to utilize a CTE or TempTable if possible.

Rahul · Accepted Answer · 2018-08-21 14:01:44Z

4

You can use that as inner query like

SELECT 
    myVar/2 AS half,
    myVar/3 AS third,
    myVar/4 AS fourth,
    myVar*2 AS twice
FROM (
SELECT (col1 + col2 + col3 + col4 + col5) as myvar, b.sepCol
FROM TableA ) xxx

edited Aug 21, 2018 at 14:01

answered Aug 21, 2018 at 13:10

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

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

1 Comment

Ilyes Over a year ago

I think you miss b.sepCol column here

Yogesh Sharma · Accepted Answer · 2018-08-21 13:48:46Z

2

I would use APPLY :

SELECT aa.cols/2 AS half,
       aa.cols/3 AS third,
       aa.cols/4 AS fourth,
       aa.cols*2 AS twice,
       b.sepCol
FROM [Table A] a INNER JOIN
     [Table B] b
     ON b.someCol = a.someCol CROSS APPLY
     ( VALUES (a.col1 + a.col2 + a.col3 + a.col4 + a.col5) 
     ) aa (cols);

edited Aug 21, 2018 at 13:48

answered Aug 21, 2018 at 13:10

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

1 Comment

Ilyes Over a year ago

You miss the b.sepCol column too :)

Collectives™ on Stack Overflow

SQL create variable over columns

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related