Row wise average in SQL Server

September 11, 2024

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

>Solution :

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

db<>fiddle demo

Side note: Argument type determines result type.