T-SQL 101: 86 Summarizing data with SUM, AVG, MIN, MAX

I mentioned in a previous post that COUNT was an aggregate. The other common aggregates are shown in this table, and no surprise what they do.

SUM adds up or totals the values.

AVG calculates the average of the values.

MIN works out the minimum value.

MAX works out the maximum value.

But if you’ve started to think about how SQL Server works, you might be wondering about what happens with NULLs.

Aggregates, in general, ignore NULLs.

For SUM, what happens is pretty obvious. MIN and MAX return the minimum and maximum of the values that are not NULL.

And AVG calculates the average without the NULL values. Specifically, that means that if I have the values:

30, NULL, 40, NULL, 50

The average is 40. That’s because the total of the non-NULL values is 120 and the count of non-NULL values is 3, so the average is 120 / 3.

You can also use these aggregates to create other aggregates. For example, some database management systems include a RANGE aggregate. That’s the range of values, or the difference between the largest and smallest values. So MAX(value) - MIN(value) would give you the equivalent of RANGE(value).

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2021-02-22