How do I Average the values across a row when some values might be Null?

Question

long time listener, first time caller here...

I am trying to use SQL to calculate the average of several values across a row. All suggestions I've seen thus far say to simply Sum the values and divide by the number of values used, which would work except that it is common for some of my values to be NULL, in which case this calculation does not work. Is there a SQL statement I can use to get the average per Identifier per date across a row of values when some of those values may be NULL?

in attached image from Excel - Excel properly captures the average and ignores the NULL values. Can SQL do the same and get the same values?

enter image description here

Tried known suggestions for summing the values and dividing by the number of values, which does not work when those values are NULL.

Answer 1

Do not expect SQL rows to work like Excel rows. SQL is more about all the data in a column, so if you want to sum values in a single column you're good to go. If you're working with multiple columns you're going to have to explicitly reference each of them, and if you're doing that you can easily do the math to create the result column (eg SELECT A,B,C, (A+B+C)/3 AS result FROM TABLE)

Normally you'd have a value for many records, and then use SQL built-in commands like AVG or SUM to produce the answer.

Given this, I think your best approach is to export the SQL data into Excel and operate on it there.

In SQL NULL is not the same as 0 (NULL in SQL is really a "does not exist" value, so that the average of 3 entries '5', '7', and 'NULL' will give an average of 6, not 4)

You can convert a NULL into 0 using the IsNull operator, eg IsNull(col,0))

Answer 2

You can use a combination of sum, coalesce and conditional aggregation.

This has been UPDATED to account for all null values. Basically, wrap the denominator with a NULLIF.

create table my_data (
  identifier integer, 
  date date, 
  value1 double precision, 
  value2 double precision, 
  value3 double precision, 
  value4 double precision, 
  value5 double precision, 
  value6 double precision
);
  
insert into my_data values 
(123, '2023-08-17', 4.9176, 5.0943, 5.0925, 3.6750, 3.7625, 3.3895), 
(987, '2023-08-17', 5.2808, null, 5.3000, 5.8750, null, 6.4117), 
(222, '2023-08-17', null, null, null, null, null, null); 

select identifier, date, 
    sum(
    coalesce(value1, 0) + 
    coalesce(value2, 0) + 
    coalesce(value3, 0) + 
    coalesce(value4, 0) + 
    coalesce(value5, 0) + 
    coalesce(value6, 0)
    ) / 
    nullif(sum(
    case when value1 is null then 0 else 1 end +
    case when value2 is null then 0 else 1 end + 
    case when value3 is null then 0 else 1 end + 
    case when value4 is null then 0 else 1 end + 
    case when value5 is null then 0 else 1 end + 
    case when value6 is null then 0 else 1 end
    ), 0) as average
from my_data
group by identifier, date
order by identifier

identifier	date	average
123	2023-08-17	4.3219
222	2023-08-17	null
987	2023-08-17	5.716875

fiddle

Answer 3

I eventually found a method that worked using some variations of suggestions here. Essentially, I added up the values in a row using a CASE WHEN VALUE_1 IS NOT NULL THEN VALUE_1 ELSE 0 END + CASE WHEN VALUE_2 IS NOT NULL THEN VALUE_2 ELSE 0 END +" and so on...then added up the values in a row where not null by 'CASE WHEN VALUE_1 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN VALUE_2 IS NOT NULL THEN 1 ELSE 0 +" and so on... used that to create a table that provided the sum of the values across the row as well as the sum of the NOT NULL cells across the row, from which I will add an additional statement like SUM(TOTAL_VALUES/TOTAL_COUNT) which will give me my average. A little bit of a windy path to get there, but it works.

Thanks all!

Answer 4

AVG() is the function that you are talking about, and it exists in SQL standard, but (as well as SUM()) works vertically, not horizontally. It represents the so called aggregate functions. Which means you need to give it a column name and it will return the average value of that column for all the rows returned by your SQL statement or for the group identified by GROUP BY parameters of your SQL statement. See official docs.

You seem to calculate the average value of several values in each row, so the problem is not with the SUM function, but with the addition operator: when you add several values and one of them is NULL, the whole expression becomes NULL. This is normal, in SQL NULL is an unknown value and if you add unknown to a known value, the result becomes unknown.

To overcome this, you can either

• organize values vertically, as naturally designed in SQL, or
• rewrite the statement to add and count only known values:

(
  ISNULL(VALUE_1, 0) +
  ... +
  ISNULL(VALUE_6, 0)
)
/
(
  IIF(VALUE_1 IS NULL, 0, 1) +
  ...
  IIF(VALUE_6 IS NULL, 0, 1)
)

And to avoid the division by zero error (in case all 6 values are NULL), we need to wrap the whole expression in additional IIF:

IIF(
  VALUE_1 IS NULL AND
  ...
  VALUE_6 IS NULL,
  NULL,
  (the_above_expression)
)