Be sure to SUBSCRIBE here to never miss another article on data science guides, tricks and tips, life lessons, and more!
In this article, we’re focusing on the fundamentals of SQL, particularly aggregate functions.
What are Aggregate Functions in SQL?
Aggregate functions are functions that are performed over one or more values and return a single value. When an aggregate function is used along with a GROUP BY clause, it can return one or more values. You’ll have a better understanding of this after going through several examples in this article.
There are five main types of aggregate functions that are essential for SQL coding:
- COUNT()
- COUNTIF()
- MIN() / MAX()
- SUM()
- AVG()
For this article, we’re going to continue with our imaginary company, Sandy Shores (if you caught our webinar on operational analytics at every stage, you know this company well), which rents beach chairs.
With that said, let’s dive into it!
1. COUNT()
COUNT returns the number of rows in a column, not including NULL values. If you wanted to only count the distinct number of values in a column, you could use COUNT(DISTINCT ).
Let’s see how Sandy Shores might use these functions. Suppose Sandy Shores has the following table called transactions, which tracks transactions for chair rentals.
- date represents the date of the transaction
- customer_id represents a unique id for each distinct customer
- discount_used is TRUE if the customer used a discount and FALSE if not
- chairs represent the number of chairs that were rented
- amount represents the total amount of the transaction

Suppose Sandy wanted to get the total number of transactions from July 1 to July 8. She could run the following query:
SELECT COUNT(customer_id) FROM transactions
This would return the number of rows which is equal to 8.
Simple right? What if Sandy wanted to get the total number of UNIQUE customers?
SELECT COUNT(DISTINCT customer_id) FROM transactions
This would return 7 because the customer with the id 894458 made two transactions, one on the fourth and one on the eighth.
We can extend COUNT even further with the following function:
Be sure to SUBSCRIBE here to never miss another article on Data Science guides, tricks and tips, life lessons, and more!
2. COUNTIF()
COUNTIF is an extension of COUNT where it returns the number of rows that satisfy the condition.

Let’s go back to our table, transactions. Suppose Sandy wanted to count the number of transactions where a discount was used and the number of transactions where a discount wasn’t used. This would be a perfect time to use the COUNTIF function:
SELECT COUNTIF(discount_used = TRUE) as num_discount
, COUNTIF(discoun_used = FALSE) as num_normal
FROM transactions
This would return the following output:

Now that you know how to use COUNT and COUNTIF, let’s dive into a similar, yet different, function:
3. SUM()
SUM returns the sum of non-null values – in other words, it adds up all of the values in a column. Don’t confuse this with COUNT. COUNT returns the number of rows in a column, while SUM adds up all of the values in a column.
There are several special cases when using SUM that you should know about:
- SUM() returns NULL if the column only has NULLs
- SUM() returns NULL if the column has no rows
- SUM() returns Inf/-Inf if the column contains Inf/-Inf
- SUM() returns NaN if the column contains a NaN
- SUM() returns NaN if the column has a combination of Inf and -Inf
SUM() is incredibly valuable when you want to know the total of ANYTHING. Let’s see how Sandy Shores can use SUM() to better understand her business.

Suppose Sandy wanted to get the total amount that she made from July 1 to July 8. She could simply run the following query:
SELECT SUM(amount)
FROM transactions
This would return a total of $149. What if she wanted to sum the total amount when a discount was applied vs wasn’t?
SELECT discount_used, SUM(amount) as total_amount
FROM transactions
GROUP BY discount_used
This would return the following:

And if we were to add these two numbers up, it also adds to $149! Superb!
4. AVG()
AVG simply returns the average of a column with non-null values. Mathematically speaking, AVG sums the values of a given column and then divides it by the corresponding number of rows.
AVG calculates a central tendency called the mean. This is extremely useful when you want to know how a particular metric is performing on average. For example, in a business setting you might want to know the following:
- You want to see if the average amount spent per transaction is growing over time
- You want to see if the average response time for your call center is decreasing
- You want to see if the average error rate for production is decreasing
Let’s see how Sandy Shores might use AVG to drive her business decision-making.

Suppose Sandy wanted to get the average number of chairs per transaction, as well as the average amount per transaction. She could run the following query:
SELECT AVG(chairs) as avg_chairs
, AVG(amount) as avg_amount
FROM transactions

This is awesome! But to extend this analysis even further, let’s look at two more functions that we can use with AVG:
Be sure to SUBSCRIBE here to never miss another article on data science guides, tricks and tips, life lessons, and more!
5. MIN() / MAX()
MIN and MAX simply return the minimum value and maximum value respectively for a column.
We previously went over AVG and how it provides the central tendency of a given column. MIN and MAX are very useful functions that complement AVG because they provide the range for a given column, allowing you to understand the variance as well as the mean.

Now, let’s get the MIN and MAX for chairs and amount, so that Sandy has a range of values for each, in addition to the average that she already calculated:
SELECT MIN(chairs) as min_chairs
, MAX(chairs) as max_chairs
, MIN(amount) as min_amount
, MAX(amount) as max_amount
FROM transactions
This would result in the following:

Now Sandy knows that she can expect an average of 2 chairs per customer with a minimum of 1 chair and a maximum of 4 chairs. Similarly, she can expect an average amount of $18.63 with a minimum of $10 and a maximum of $40.
Thanks for reading!
Be sure to SUBSCRIBE here to never miss another article on data science guides, tricks and tips, life lessons, and more!
Not sure what to read next? I’ve picked another article for you:
and another one:
– Terence Shin
- If you enjoyed this, SUBSCRIBE to my Medium for exclusive content!
- Likewise, you can also FOLLOW me on Medium
- Sign up for my personal newsletter
- Follow me on LinkedIn for other content