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:
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.
SELECT COUNT(*) FROM Customers;
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.
SELECT Customer_Name, MAX(Total_Purchase_Amount) FROM Customers;
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.
SELECT Customer_Name, MIN(Total_Purchase_Amount) FROM Customers;
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!