How to Use Aggregate Functions in Your Queries

If you’re new to writing SQL queries, you may not know about aggregate functions. These handy tools can provide you with some quick information about your tables. For example, you can use aggregate functions to find the number of rows in your table or to do simple calculations like find the minimum or maximum values in a column. Let’s take a closer at how aggregate functions work:

How Aggregate Functions are Different from Basic Queries – If you’re learning to write queries, you’re probably comfortable with the “SELECT * FROM Tablename” syntax to return all the rows in a table. When you use a function in your query, you’re only going to get one result (unless you include a group by condition on your query, but we won’t get that far in this tutorial). By using a function, you’re looking for one value as your result. Let’s walk through examples of the most common aggregate functions and how they work using this simple Customers table:

Customer_ID

Customer_Name

Total_Purchase_Amount

1

Toby Thomas

1000

2

Susan Sanders

500

3

Callie Calhoun

200

4

Will Webster

450

Count Function – The count() function returns the number of rows in the table. Use the count function to answer business questions like how many customers there are. Add a WHERE clause to your query to return the count only for a specific condition, like to return a count of customers in California.

Sample Query:

SELECT COUNT(*) FROM Customers;

Result:

4

Max Function – The max() function returns the maximum value in the column you specify in your query. Let’s select the customer with the largest purchase amount.

Sample Query:

SELECT Customer_Name, MAX(Total_Purchase_Amount) FROM Customers;

Result:

Toby Thomas 1000

Min Function – The min() function works just like the max() function, but instead returns the smallest value. Selecting the smallest purchase amount will return Callie Calhoun’s $200 purchase, as shown below.

Sample Query:

SELECT Customer_Name, MIN(Total_Purchase_Amount) FROM Customers;

Result:

Callie Calhoun 200

Other Aggregate Functions – These examples should give you a basic understanding of how aggregate functions work in SQL queries. Here are some other useful functions:

AVG() – returns the average value for the column

SUM() – returns the sum of the column

FIRST() – returns the first value in the column

LAST() – returns the last value in the column

Once you’re comfortable with how functions work, you can add WHERE clauses and GROUP BY statements to your queries in order to perform more complicated data manipulation. Adding an understanding of aggregate functions to your SQL repertoire will give you the ability to answer complicated business questions in mere seconds!