What will be the Result of AVG() if any records have NULL values

 

How to Count SQL NULL values in a column?

The COUNT() function is used to obtain the total number of the rows in the result set. When we use this function with the star sign it count all rows from the table regardless of NULL values. Such as, when we count the Person table through the following query, it will return 19972.

1	SELECT COUNT(*) AS [Total Number of Rows] FROM Person.Person

On the other hand, when we use the COUNT() function with a column name it counts only the non-NULL values in that column.

1	SELECT COUNT(Title) AS [Total Number of Title] FROM Person.Person

In order to count NULL values of a column, we can use the following query.

1 2 3 4

SELECT SUM(CASE WHEN Title is null THEN 1 ELSE 0 END) AS [Number Of Null Values]     , COUNT(Title) AS [Number Of Non-Null Values]     FROM Person.Person

AVG() function and SQL NULL values

The AVG () is used to calculate the average value of a result set, that is, it sums all the values ​​in that result set and divides that sum by the number of rows. One point to note about the AVG() function calculation is that NULL values will not be included in the average calculation.

1 2 3 4 5 6 7 8 9 10

CREATE TABLE DailyFee (WeekDayName VARCHAR(50), Fee MONEY DEFAULT NULL)   INSERT INTO DailyFee VALUES ('Monday', 20 ), ( 'Tuesday', 60 ), ( 'Wednesday', NULL ), (  'Thurusday', 40 ), (  'Friday', 80 )

When we calculate the weekly average fee, the Wednesday fee is not included in the calculation.

1 2 3

SELECT * FROM DailyFee   SELECT AVG(Fee) AS AverageFee FROM  DailyFee

If we want to include the NULL values in the calculation, we can use the COALESCE function.

1 2 3

SELECT * FROM DailyFee   SELECT AVG(COALESCE(Fee,0)) AS AverageFee FROM  DailyFee