What is an Aggregate Function?
SQL aggregate functions will seem very familiar if you have worked with spreadsheets.
Have you ever used
SUM in Google Sheets or Excel? The
SUM function exists in SQL as well, and is called an aggregate function.
Aggregate functions do a particular task across database rows. For example, say you run a yearly fundraiser. You have a database of donors along with the amount they donated each year.
You might use the
COUNT function to determine how many donations were received, or the
SUM function to find out how much you have made in total this year.
We will use the following small data set for illustrative purposes.
In this article, we will cover the following aggregate functions:
The COUNT function
COUNT function returns a count of rows. In its simplest form,
COUNT counts the total number of rows in your table.
To get that value from our donor table, you would run the following query:
SELECT COUNT(*) FROM donors. This will return the total number of donors, which in this case is 6. Here, of course, you could just count the rows in this example, but let's imagine there are a lot more rows.
You might want to count only certain rows, however. In our donor database example, say you want to count the number of donors who have an email listed.
If you run the query
SELECT COUNT(email) FROM donors, you will get 4, which is the total number of donors with non-null values in the email column.
Keep in mind that unless you use an alias, the column returned will simply be labeled "count". If you want a more descriptive name, run a query using
AS to create an alias, for example
SELECT COUNT(email) FROM donors AS email_count.
The SUM function
SUM is a super handy aggregate function you can use to add together numerical values from different rows.
In our donor database, you could use
SUM to add up all of the 2021 donations by running the query
SELECT SUM(donation_2021) FROM donors. Note that
NULL values, so the result of this query will be 7175.
SUM, like other aggregate functions, works across rows, not columns.
So in our example, you could use it to add together everyone’s donations from 2021 (represented by the
donation_2021 column), but not to add together all of one person's donations from the
donation_2020 column and the
The MIN and MAX functions
As you might guess, you can use MIN and MAX to find the minimum and maximum values in a particular column of a database.
In our example data, imagine you want to find the minimum and maximum donation amounts for 2021. You could do this by running the query:
SELECT MIN(donation_2021) AS "Minimum donation 2021", MAX(donation_2021) AS "Maximum donation 2021" FROM donors.
Note that in this example, we are assigning aliases to the returned columns using quotation marks. Quotation marks are not necessary if your alias has no spaces in it, but we are using quotes here so we can use spaces.
An interesting side note: you can use
MAX on non-numeric values.
MIN will find the smallest number, the closest letter to A, or the earliest date.
MAX will find the largest number, the closest letter to Z, or the latest date. Very handy!
The AVG function
AVG function averages numeric values from a particular column. As with
SUM, it ignores
To get an average of all of the donations from 2020, you can run the following query:
SELECT AVG(donation_2020) FROM donors. The result of this query would be 1435.
As you have seen, aggregate functions are easy and useful tools for analyzing data in SQL.
SUM , and
COUNT are an important addition to your SQL skillset.