I have a table in SQL Server named MyTable
.
I want to calculate the row wise average of two columns (say val1
and val2
). To my knowledge, I know that this can be done as shown below in my approach. But is there any other way to do it quicker and without having to specify the denominator?
CREATE TABLE MyTable
(
var CHAR(1) CHECK (var IN ('A', 'B', 'C')),
var2 CHAR(2) CHECK (var2 IN ('AA', 'BB', 'CC')),
val1 INT CHECK (val1 IN (1, 2, 3, 4, 5)),
val2 INT CHECK (val2 IN (1, 2, 3, 4, 5))
);
INSERT INTO MyTable (var, var2, val1, val2)
VALUES ('A', 'AA', 1, 2),
('B', 'BB', 3, 4),
('A', 'BB', 1, 2),
('B', 'BB', 3, 4),
('A', 'BB', 1, 2),
('B', 'CC', 3, 4),
('A', 'CC', 1, 2),
('B', 'BB', 3, 4),
('C', 'CC', 5, 1);
SELECT
var,
var2,
val1,
val2,
(val1 + val2) / 2 AS var3
FROM
Mytable
In order to use aggregate function row-wise you can "unpivot" table with CROSS APPLY
and then calculate aggregate:
SELECT m.*, r.calc_average
FROM MyTable AS m
CROSS APPLY (SELECT AVG(s.v)
FROM (VALUES (m.val1),(m.val2)) AS s(v) -- here goes column list
) AS r(calc_average);
Side note: Argument type determines result type.