SQL Aggregate Functions Explained: Examples and Best Practices

When learning SQL, you will certainly encounter SQL aggregate functions. Aggregate functions in SQL are used to perform operations across a column of data and return a single value. These functions allow you to extract relevant insights and generate summaries without needing to analyze every single data point.

In this article, we’ll explore SQL aggregate functions and their syntax, provide real-world examples, and share best practices for using them effectively.

Data professionals often work with large datasets, and in this context, SQL aggregate functions are essential for efficiently summarizing and analyzing data. These functions help extract meaningful insights, simplify complex data structures, and make statistical analysis more manageable.

The most commonly used SQL aggregate functions are:

• SUM() - adds together all the values in a particular column.
• COUNT() - counts how many rows are in a particular column.
• AVG() - calculates the average of a group of selected values.
• MIN() - returns the lowest in a particular column.
• MAX() - returns the highest in a particular column.

Let’s dive into each of them below, along with examples and best practices.

SUM()

The SUM() function returns the total sum of a numerical column. When using SUM(), null values are considered zero, so they do not affect the result.

Imagine you have a list of orders, each with its own total cost. To find the grand total of all these orders, you can use this SQL command:

SELECT SUM(total_amount) AS total_revenue 
FROM orders;

COUNT()

The COUNT() function is used to count rows in a table. If you use COUNT(*), it counts all rows, including those with NULL values in columns.

Let’s continue with our orders dataset to find out how many total orders exist. Using *, we can select and count all rows. Here’s the query:

SELECT COUNT(*) AS total_orders  
FROM orders;  

AVG()

If you want to calculate the average (mean) value of a numeric column, you can use the AVG() function. It ignores null values entirely when performing the calculation.

Continuing with our orders dataset, let’s say we have a column named total_amount that represents the total cost of each order. To calculate the average order value, you can use the following SQL query:

SELECT AVG(total_amount) AS average_order_value  
FROM orders;

MIN()

The MIN() function returns the smallest value within a column. This can be the lowest number, the earliest date, or the alphabetically earliest non-numeric value (closest to “A”).

Using our orders dataset, if we want to find the lowest order amount in the total_amount column, we can use this SQL query:

SELECT MIN(total_amount) AS lowest_order 
FROM orders;

MAX()

The MAX() function returns the largest value within a column. Unlike MIN(), the MAX() function returns the highest number, the latest date, or the non-numeric value closest alphabetically to “Z.”

To find the highest order amount in the total_amount column from our orders dataset, you can use this SQL query:

SELECT MAX(total_amount) AS highest_order 
FROM orders;

USING Aggregate Functions with GROUP BY

Aggregate functions are often used with the GROUP BY clause of the SELECT statement. The GROUP BY clause is commonly used to group rows that have the same values in specified columns into summary rows, like finding totals or averages for each category.

Let’s assume we have the following orders table with the following columns:

order_id	customer_id	order_date	total_amount
1	101	2024-01-10	50
2	102	2024-01-11	80
3	101	2024-01-12	120
4	103	2024-01-12	200
5	101	2024-01-13	75
6	102	2024-01-13	150
7	104	2024-01-14	100

Example: Aggregate Functions with GROUP BY

Let’s say you want to calculate the total order amount and the average order amount for each customer. You can group by customer_id and apply SUM() and AVG() to get those values.

SELECT customer_id,
       SUM(total_amount) AS total_spent,
       AVG(total_amount) AS average_order_value
FROM orders
GROUP BY customer_id;

Output:

customer_id	total_spent	average_order_value
101	245	81.67
102	230	115
103	200	200
104	100	100

If you want to find out how many orders each customer has placed, you can use the COUNT() function with the following query:

SELECT customer_id,
       COUNT(*) AS number_of_orders
FROM orders
GROUP BY customer_id;

Output:

customer_id	number_of_orders
101	3
102	2
103	1
104	1

To find the smallest and largest order amounts for each customer, you can use the MIN() and MAX() functions along with the GROUP BY clause. Here’s how:

SELECT customer_id,
       MIN(total_amount) AS smallest_order,
       MAX(total_amount) AS largest_order
FROM orders
GROUP BY customer_id;

Output:

order_id	smallest_order	largest_order
101	50	120
102	80	150
103	200	200
104	100	100

For customers with only one order, the MIN() and MAX() functions return the same value because there is only one value to evaluate within the group.

Filtering with HAVING and WHERE

The WHERE clause filters rows before grouping the data. On the other hand, the HAVING clause ****is used to filter groups after the aggregation.

For example, if you want to count the orders with a total amount greater than 50 and find customers with more than one order, you can use the WHERE clause to filter orders based on the total amount and the HAVING clause to filter customers with more than one order. Here’s the SQL query:

SELECT customer_id, COUNT(*) AS number_of_orders  
FROM orders  
WHERE total_amount > 50  
GROUP BY customer_id  
HAVING COUNT(*) > 1; 

Output:

order_id	number_of_orders
101	2

Now that you have a better understanding of how SQL aggregate functions work, it’s time to put your knowledge into practice with real-world examples.

Here are a few datasets you can use to practice SQL aggregate functions and data analysis.

1. IBM HR Analytics Attrition Dataset

2. IBM Watson Marketing Customer Value Data

3. Sample Sales Data

For more exploration, you can check out this article: Best Platforms to Practice SQL. It provides a list of platforms to experiment with queries and explore datasets effectively.

To effectively use SQL aggregate functions, here are some helpful practices:

• Use meaningful column names for clarity.
• Before applying aggregate functions, ensure that the columns you’re working with have the correct data types.
• You can use multiple aggregate functions together to get a more useful analysis.
• Avoid overusing SELECT *; specify only the columns you need.
• Make sure to group the data by relevant columns so that the results make sense.
• Understand how NULL values are handled by different aggregate functions.

By following these practices, you’ll be able to use SQL aggregate functions more effectively and avoid common pitfalls.

SQL aggregate functions are helpful tools for summarizing and analyzing data in relational databases. Learning how to use these functions can make it easier to find useful insights, make better decisions, and improve business results.

This guide is here to help you get started with SQL and use aggregate functions to your advantage. Knowing SQL is a valuable skill that can give you an edge in today’s job market. Happy learning and practicing!