By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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
| var | var2 | val1 | val2 | var3 |
|---|---|---|---|---|
| A | AA | 1 | 2 | 1 |
| B | BB | 3 | 4 | 3 |
| A | BB | 1 | 2 | 1 |
| B | BB | 3 | 4 | 3 |
| A | BB | 1 | 2 | 1 |
| B | CC | 3 | 4 | 3 |
| A | CC | 1 | 2 | 1 |
| B | BB | 3 | 4 | 3 |
| C | CC | 5 | 1 | 3 |
SELECT m.*, r.calc_average
FROM MyTable AS m
CROSS APPLY (SELECT AVG(s.v)
FROM (VALUES (m.val1),(m.val2)) AS s(v)
) AS r(calc_average);
| var | var2 | val1 | val2 | calc_average |
|---|---|---|---|---|
| A | AA | 1 | 2 | 1 |
| B | BB | 3 | 4 | 3 |
| A | BB | 1 | 2 | 1 |
| B | BB | 3 | 4 | 3 |
| A | BB | 1 | 2 | 1 |
| B | CC | 3 | 4 | 3 |
| A | CC | 1 | 2 | 1 |
| B | BB | 3 | 4 | 3 |
| C | CC | 5 | 1 | 3 |