Learn SQL: Microsoft SQL Server - Episode 8: NULL values in Aggregation
Tags: sql, database, beginners, challenge
Published: 2021-11-06 13:40:19

![welcome](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pe5novza5mehmopymkle.gif)

In this episode we will be discussing the impact of **NULL** values in _Aggregation_. **_Null_** value means that a column does not contain any value. It is not an empty string or 0, it is the absence of any value.

![skid-1-cover](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wn4vsqbl4qgfghvvt8tz.png)

####NULL

**_Null_*** values are excluded from calculations. Whenever we does aggregations such as **SUM**, the **NULL** values are not participants in these calculations.

![skid-4](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1owno79rqpckmqyq96dr.gif)

This is **not** an issue when calculations are being performed on **MIN**, **MAX** and **SUM**, because when **NULL** is ignored is does not alter the calculation in anyway since it is the equivalent of 0 in these specific calculations.

![diagram-1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ydqd8odqgwdg1d0sf0y6.gif)

This is an issue when calculations are being performed on **Count** and **AVG**, <u>this is the reason for the episode</u>; to demonstrate the impact **NULL** values have in aggregations such as **Count** and **AVG**.

Let’s see this in practice in <abbr title="SQL Server Management Studio">SSMS</abbr>.

![skid-11](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/o8nvgz4n5o9nvoelyib7.gif)

>I have created a simple table to better explain the impact of NULL values, since the size of most tables in AdventureWorks are large which in turn makes it difficult to immediately see the impact they have.

![practical-1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gzjhpkxdaijk8lfiof3h.gif)

If we check to see the count of the **OrderSales** table, we will see it picks up all 4 of the values. But if we check to see the count of only the _order value_ column, then we are receive only 3 in total. This means that the **Count** function only factored in **NON-NULL** values.

![skid-11](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/o8nvgz4n5o9nvoelyib7.gif)

**MIN** will calculate the minimum value.

![practical-2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vfjlu50z3i781cdo42br.gif)

**MAX** will calculate the maximum value.

![practical-3](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/34kppw6ik59mxq2rqr9e.gif)

**SUM** will calculate the sum of all values.

![practical-4](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/neqxy4pbxawmxuz50bof.gif)

However, **AVG** will not work as presumed, instead of dividing by four rows it will not accept **NULL** as valid and therefore not include it in its calculation. Unfortunately because of this we end up with invalid data.

![practical-5](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/80hzag3tzytpdumpoukr.gif)

**_Average_** is suppose to take the **SUM** of all the values which is 60 and divide them by 4, which is the total amount of rows. We should receive 15. 

However, we receive back 20. Since the row with a **NULL** value was not included in the calculation, therefore we only divided the total of 60 by 3.

![skid-4](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1owno79rqpckmqyq96dr.gif)

>Keep this in mind when you are querying your database, especially when working with **AVG**, since this could lead to inaccurate data. 

This can be especially troublesome in a large dataset such as _AdventureWorks_, this behaviour is not immediately apparent and can be easily missed or forgotten.

![skid-1-cover](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wn4vsqbl4qgfghvvt8tz.png)

I hope this episode was helpful, I will try to keep this little bit of knowledge at the back of my mind whenever I am querying databases and I hope you do to. Till next time...

![meditation](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/n73b5kzvhre8e0zfsaoe.gif)