MSSQL - What are aggregate functions in SQL?

When working with databases, managing and analyzing large amounts of data efficiently is crucial. This is where SQL (Structured Query Language) comes into play, SQL provides a set of powerful tools and functions to retrieve and manipulate data. One such group of functions is aggregate functions, in this blog post, we will take a look at aggregate functions and how they can be used in SQL queries.

Aggregate functions in SQL are built-in functions that allow you to perform calculations on values within your database tables. These functions take a set of values as input and return a single value as output, summarizing the data in some way. They are often used in conjunction with the SELECT statement to compute statistical information or summarize data in various ways. Except for COUNT(*), aggregate functions ignore null values.

Different types of Aggregate Functions

Let us take a look at some of the most commonly used aggregate functions in SQL. We will use the following reference table called MyEntities in MyDb:

Above an other markdown tables in this post are generated using www.tablesgenerator.com

COUNT
The COUNT function is used to determine the number of rows in a table or the number of values in a specific column. A simple query would be SELECT COUNT(Id) FROM MyDb.dbo.MyEntities. COUNT is the only aggregate functions that does not ignore NULL values, but only used with a wildcard (*) as in COUNT(*) which simply counts all rows. Given this, counting SomeNumber using the above reference table would give the result 2, and counting using a wildcard would give 3.

SUM
The SUM function calculates the total sum of values in a given column. It is primarily used with numeric data types and can provide insights into the total quantity, revenue, or any other accumulated value. A simple query would be SELECT SUM(SomeNumber) FROM MyDb.dbo.MyEntities. This would give the result 8 using the reference table.

AVG
The AVG function computes the average of a set of values in a column. It is particularly useful for determining the mean value, such as the average price. A simple example would be SELECT AVG(SomeNumber) FROM MyDb.dbo.MyEntities which would give the result 4 using the reference table.

MAX
The MAX function returns the highest value in a column. It can be used to find the maximum value in a dataset, like the highest price or the highest ID in the table. A simple example would be SELECT MAX(SomeNumber) FROM MyDb.dbo.MyEntities which would give the result 6 using the reference table.

MIN
The MIN function is the opposite of the MAX function, retrieves the lowest value from a column. It helps identify the minimum value in a dataset, such as the lowest price or the lowest ID in the table. A simple example would be SELECT MIN(SomeNumber) FROM MyDb.dbo.MyEntities which would give the result 2 using the reference table.

Using aggregate functions with group by

Aggregate functions are typically used together with the GROUP BY clause. The GROUP BY clause divides the data into groups based on one or more columns and then the aggregate functions are applied to each group separately. This allows you to perform calculations on subsets of data, enabling deeper analysis and summary. If we extend our table with a group column with two groups:

We can then apply aggregate functions to the groups

COUNT

We can use COUNT in conjunction with a group by:

SELECT COUNT(SomeNumber) FROM MyDb.dbo.MyEntities
GROUP BY [Group]

In the above we use the COUNT aggregate function and get the results 2 and 2.

NOTE: had we use COUNT(*) then the result would have been 3 and 2

SUM

We can use SUM in conjunction with a group by:

SELECT SUM(SomeNumber) FROM MyDb.dbo.MyEntities
GROUP BY [Group]

In the above we use the SUM aggregate function and get the results 10 and 5.

AVG

We can use AVG in conjunction with a group by:

SELECT AVG(SomeNumber) FROM MyDb.dbo.MyEntities
GROUP BY [Group]

In the above we use the AVG aggregate function and get the results 5 and 2.

MAX

We can use MAX in conjunction with a group by:

SELECT MAX(SomeNumber) FROM MyDb.dbo.MyEntities
GROUP BY [Group]

In the above we use the MAX aggregate function and get the results 6 and 3.

MIN

We can use MIN in conjunction with a group by:

SELECT MIN(SomeNumber) FROM MyDb.dbo.MyEntities
GROUP BY [Group]

In the above we use the MIN aggregate function and get the results 4 and 2.

That is all!

Aggregate functions in SQL are great tools to facilitate data analysis and summarization. By leveraging these functions, you can derive meaningful insights, calculate statistics, and summarize data efficiently. Whether you need to count rows, calculate totals, find averages, or determine maximum and minimum values, aggregate functions are here to help!

As always let me know what you think in the comment down below, I read them all!