SQLServer Calculate Average of Multiple Columns

Question

Hi,

I have generated a table using PIVOT and the ouput of columns are dynamic. One of the output is as given below:

user       test1   test2    test3 
--------------------------------
A1          10      20       30  
A2          90      87       75
A3          78      12       34

The output of above table represents a list of users attending tests. The tests will be added dynamically, so the columns are dynamic in nature.

Now, I want to find out average marks of each user as well as average marks of each test.

I am able to calculate the average of each test, but got puzzled to find out the average of each user.

Is there a way to do this??

Please help.

Mahesh

Answer 1

You can add the marks for each user then divide by the number of columns:

SELECT
    user, 
    (test1 + test2 + test3) / 3 AS average_mark
FROM users

Or to ignore NULL values:

SELECT
    user,
    (ISNULL(test1, 0) + ISNULL(test2, 0) + ISNULL(test3, 0)) / (
         CASE WHEN test1 IS NULL THEN 0 ELSE 1 END +
         CASE WHEN test2 IS NULL THEN 0 ELSE 1 END +
         CASE WHEN test3 IS NULL THEN 0 ELSE 1 END
    ) AS average_mark
FROM users

Your table structure has two disadvantages:

• Because your table structure is created dynamically you would also have to construct this query dynamically.
• Because some students will not have taken all tests yo may have some NULL values.

You may want to consider changing your table structure to fix both of these problems. I would suggest that you use the following structure for your table:

user   test    mark
-------------------
A1     1       10
A2     1       90
A3     1       78
A1     2       20
A2     2       87
A3     2       12
A1     3       30
A2     3       75
A3     3       34

Then you can do this to get the average mark per user:

SELECT user, AVG(mark) AS average_mark
FROM users
GROUP BY user

And this to get the average mark per test:

SELECT test, AVG(mark) AS average_mark
FROM users
GROUP BY test

Answer 2

Can you do it on your data source before you pivot it?

Answer 3

The simple answer is to UNPIVOT the same way you just PIVOTed. But the best answer is to not do the PIVOT in the first place! Store the unpivoted data in a table first, then from that do your PIVOT and your average.