SQL create variable over columns

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.

2 Answers
2

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


I would use APPLY :

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);


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.