sqlsql-serveraveragerowwise

Row wise average in SQL Server


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.