How to get average cumulative value for several columns in SQL Server?-CodePudding

Microsoft SQL Server 2019

Table example:

name	Q1	Q2	Q3	Q4
test1	43	-3	NULL	NULL
test2	35	33	NULL	NULL
test3	32	35	21	NULL
test4	32	39	NULL	NULL
test5	NULL	NULL	NULL	NULL
test6	18	-20	37	NULL
test7	NULL	14	NULL	NULL
test8	57	60	NULL	NULL

I need to get an output table like:

mean12	mean123	mean1234
30	29	29

Where mean12 = rounded average from all not NULL values of columns Q1 and Q2.

It counts according to this logic:

ROUND((ROUND((43 35 32 32 18 57)/6)   ROUND((-3 33 35 39-20 14 60)/7))/2) = 
ROUND((ROUND(36.16)   ROUND(22.57))/2) =
ROUND((36 23)/2) = ROUND(29.5) = 30

The rounded average of each column is counted first, and then the rounded average between them.

mean123 = rounded average from all not NULL values of columns Q1, Q2 and Q3;

mean1234 = rounded average from all not NULL values of columns Q1, Q2, Q3 and Q4

I tried to do it like

SELECT 
    ROUND(((ROUND(AVG(Q1), 0)   ROUND(AVG(Q2), 0)) / 2), 0) AS Mean12,
    ROUND(((ROUND(AVG(Q1), 0)   ROUND(AVG(Q2), 0)   ROUND(AVG(Q3), 0)) / 3), 0) AS  Mean123,
    ROUND(((ROUND(AVG(Q1), 0)   ROUND(AVG(Q2), 0)   ROUND(AVG(Q3), 0)   ROUND(AVG(Q4), 0)) / 4), 0) AS Mean1234

but it is not working correctly, because of NULLs in column Q4:

mean12	mean123	mean1234
30	29	NULL

I can't figure out how to get the average in any other way. Help me please!

CREATE TABLE test(
   name VARCHAR(5) PRIMARY KEY
  ,Q1   INTEGER 
  ,Q2   INTEGER 
  ,Q3   INTEGER 
  ,Q4   INT 
);
INSERT INTO test(name,Q1,Q2,Q3,Q4) VALUES
 ('test1',NULL,NULL,NULL,NULL)
,('test2',43,-3,NULL,NULL)
,('test3',35,33,NULL,NULL)
,('test5',32,39,NULL,NULL)
,('test6',NULL,NULL,NULL,NULL)
,('test7',18,-20,37,NULL)
,('test8',NULL,14,NULL,NULL)
,('test9',57,60,NULL,NULL);

CodePudding user response：

Please try the following solution.

It is using a row value constructor approach.

It is excluding NULL values from the calculations.

SQL #1

-- DDL and sample data population, start
DECLARE @tbl TABLE (name VARCHAR(20) PRIMARY KEY, Q1 INT, Q2 INT, Q3 INT, Q4 INT);
INSERT INTO @tbl (name, Q1, Q2, Q3, Q4) VALUES
('test1', 43,   -3,   NULL, NULL),
('test2', 35,   33,   NULL, NULL),
('test3', 32,   35,   21,   NULL),
('test4', 32,   39,   NULL, NULL),
('test5', NULL, NULL, NULL, NULL),
('test6', 18,   -20,    37, NULL),
('test7', NULL, 14,   NULL, NULL),
('test8', 57,   60,   NULL, NULL);
-- DDL and sample data population, end

SELECT mean12 = AVG(mean12), mean123 = AVG(mean123), mean1234 = AVG(mean1234)
FROM @tbl AS t
CROSS APPLY (VALUES 
         (t.Q1),
         (t.Q2)
         ) AS t1(mean12)
CROSS APPLY (VALUES 
         (t.Q1),
         (t.Q2),
         (t.Q3)
         ) AS t2(mean123)
CROSS APPLY (VALUES 
         (t.Q1),
         (t.Q2),
         (t.Q3),
         (t.Q4)
         ) AS t3(mean1234);

Output

mean12	mean123	mean1234
28	28	28

SQL #2

;WITH rs AS
(        
    SELECT mean1 = AVG(Q1), mean2 = AVG(Q2), mean3 = AVG(Q3), mean4 = AVG(Q4) 
    FROM @tbl
)
SELECT Mean12 = (mean1   mean2) / 2
    , Mean123 = (mean1   mean2   mean3) / 3
    , Mean1234 = (mean1   mean2   mean3   COALESCE(mean4, 0)) / (3   IIF(mean4 IS NULL,0,1))
FROM rs;

Output

Mean12	Mean123	Mean1234
29	29	29

CodePudding user response：

By combining the solutions suggested above, I achieved exactly the logic I needed:

DECLARE @table TABLE(AvQ1 Float, AvQ2 Float, AvQ3 Float, AvQ4 Float);
    INSERT INTO @table
    SELECT ROUND(AVG(QVPM1), 0), ROUND(AVG(QVPM2), 0), ROUND(AVG(QVPM3), 0),  ROUND(AVG(QVPM4), 0) FROM @test
    
    SELECT mean12 = ROUND(AVG(mean12), 0), mean123 = ROUND(AVG(mean123), 0), mean1234 = ROUND(AVG(mean1234), 0)
    FROM @table AS t
    CROSS APPLY ( VALUES 
             (t.AvQ1),
             (t.AvQ2)
             ) AS t1(mean12)
    CROSS APPLY (VALUES 
             (t.AvQ1),
             (t.AvQ2),
             (t.AvQ3)
             ) AS t2(mean123)
    CROSS APPLY (VALUES 
             (t.AvQ1),
             (t.AvQ2),
             (t.AvQ3),
             (t.AvQ4)
             ) AS t3(mean1234);