Average in SQL Server with null value multiple columns

Question

I want to take average of multiple columns such that if there is null value then ignore that and take remaining value as take average.

For example if I have 10,NULL as values then I want 10 as average, I don't want the null value to be considered as 0 and then take average as 10+0 -> 5, I want the average to be 10.

This is a dummy table, I have multiple columns like Month1.... Month 1000 and I don't want to hardcode any values while calculating average by dividing the number of column.

My output from this query is wrong:

CREATE TABLE Dummy_tab 
(
    empid int,
    Month1 int,
    Month2 int,
    Month3 int,
);
 
INSERT INTO Dummy_tab 
VALUES (1, NULL,10, 20), (2, NULL,NULL, 20), (3, 10,20, 30);
  
SELECT
    empid,
    AVG(Month1 + Month2 + Month3)
FROM
    Dummy_tab
GROUP BY 
    empid

Snippet for the desired output along with what my output is coming

Zhorov · Accepted Answer · 2020-10-09 20:51:01Z

5

One possible option is to use VALUES table value constructor - to unpivot the columns into rows and to calculate the average value for all columns, excluding the columns with NULL values:

Statement:

SELECT t.empid, c.Average
FROM Dummy_tab t
CROSS APPLY (
   SELECT AVG(v.Month) AS Average
   FROM (VALUES (t.Month1), (t.Month2), (t.Month3)) v (Month)
) c

Result:

empid   Average
1       15
2       20
3       20

edited Oct 9, 2020 at 20:51

answered Oct 9, 2020 at 19:51

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Brian Pressler Over a year ago

I like this one... simple :)

Brian Pressler Over a year ago

@shawnt00 Why would it triplicate rows? And can you be more specific about what you mean by "mess with other aggregations you got going on"?

shawnt00 Over a year ago

I wasn't reading close enough. The whole time I had been thinking that the average was taken across both rows and columns and I hadn't noticed the average was done inside the cross apply.

Brian Pressler · Accepted Answer · 2020-10-09 20:09:38Z

0

I think this should do what you are trying to accomplish:

SELECT
    empid,
    (SUM(ISNULL(Month1,0)) 
        + SUM(ISNULL(Month2,0)) 
        + SUM(ISNULL(Month3,0)))
     / 
    (ISNULL(COUNT(Month1),0)
        + ISNULL(COUNT(Month2),0)
        + ISNULL(COUNT(Month3),0)
    )
FROM
    Dummy_tab
GROUP BY 
    empid

answered Oct 9, 2020 at 20:09

Brian Pressler

6,7232 gold badges21 silver badges42 bronze badges

Comments

rens · Accepted Answer · 2020-10-09 20:12:22Z

0

Shawnt00s code work, if you correct his typo.

SELECT
    empid,
    SUM(
        CASE WHEN Month1 IS NULL THEN 0.0 ELSE Month1 END +
        CASE WHEN Month2 IS NULL THEN 0.0 ELSE Month2 END +
        CASE WHEN Month3 IS NULL THEN 0.0 ELSE Month3 END 
    ) /
    SUM(
        CASE WHEN Month1 IS NULL THEN 0.0 ELSE 1.0 END +
        CASE WHEN Month2 IS NULL THEN 0.0 ELSE 1.0 END +
        CASE WHEN Month3 IS NULL THEN 0.0 ELSE 1.0 END
    ) AS Average
FROM
    Dummy_tab
GROUP BY 
    empid

answered Oct 9, 2020 at 20:12

rens

437 bronze badges

Comments

shawnt00 · Accepted Answer · 2020-10-09 20:41:58Z

0

When dealing with integers and division you need to cast either the numerator or denominator to a decimal value before doing the division.

For the average of three values in a single row:

SELECT
    empid,
    (
        -- coalesce(MonthX, 0) would work too
        CASE WHEN Month1 IS NULL THEN 0.0 ELSE Month1 END +
        CASE WHEN Month2 IS NULL THEN 0.0 ELSE Month2 END +
        CASE WHEN Month3 IS NULL THEN 0.0 ELSE Month3 END +
    ) * 1.0 /
    (
        CASE WHEN Month1 IS NULL THEN 0.0 ELSE 1.0 END +
        CASE WHEN Month2 IS NULL THEN 0.0 ELSE 1.0 END +
        CASE WHEN Month3 IS NULL THEN 0.0 ELSE 1.0 END
    ) AS Average
FROM
    Dummy_tab
GROUP BY 
    empid;

Or more generally you could apply this to a group with multiple rows. SUM() handles the nulls values automatically:

SELECT
    empid,
    (SUM(Month1) + SUM(Month2) + SUM(Month3)) * 1.0 /
        (COUNT(Month1) + COUNT(Month2) + COUNT(Month3)) AS Average
FROM
    Dummy_tab
GROUP BY 
    empid

edited Oct 9, 2020 at 20:41

answered Oct 9, 2020 at 19:38

shawnt00

18.1k3 gold badges23 silver badges25 bronze badges

3 Comments

Dexter1611 Over a year ago

The 1st query is not working , the second one is giving wrong output

shawnt00 Over a year ago

Yeah, it ended up doing a multiplication rather than division. Try that. First one was just typos from copying and pasting some of the lines.

shawnt00 Over a year ago

@Dexter_1234, I thought you wanted an average across both rows and columns. I see now that you're just looking to average three values in a single row.

Collectives™ on Stack Overflow

Average in SQL Server with null value multiple columns

4 Answers 4

3 Comments

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related