Databases are a crucial component of many websites and apps, and allow data to be stored and retrieved through computers. By using a query, you can get the specific information you need from a database, such as a list of names from an employee database, or a list of car manufacturers from a sports racing database.
There are also occasions where you may want to get information about the data, but not the data itself. Perhaps you want to know how many employees are based in each office, or who has worked for your business the longest. This is where
SQL aggregate functions come in.
In this guide, we’ll discuss the basics of
SQL aggregate functions, how they work, and how you can use them in your queries.
Note: We are using
PostgreSQL for the purposes of this article.
In order to retrieve data from a database, you need to run a query. Queries almost always begin with the word
SELECT, and contain the information about what information you want to retrieve from a database.
Here is the basic structure of a query in
SELECT name FROM employees WHERE branch = 'Albany';
Our code may return the following:
We can also change our query to get all of the employees from Boston by changing the branch, or the employee with the email address
firstname.lastname@example.org by adding a new
WHERE clause. We could also use the
ORDER BY clause to sort our database in a certain order. In short, queries are used in
SQL to retrieve data from a database.
SQL Aggregate Functions
Often, when you’re working with a database, you don’t need to see the data with which you are working. Instead, you want to get statistics about that information. For example, you may want to get a count of how many records meet certain prerequisites, or the largest value in a dataset.
SQL, these operations are referred to as aggregate functions. The five main aggregate functions used in
SUM. These functions all return a single value. Let’s break them down individually.
The COUNT function returns the total number of rows that meet a certain condition. For example, if you wanted to know how many employees work in the Stamford branch of your business, you could execute this query:
SELECT COUNT(name) FROM employees WHERE branch = "Stamford";
Our database returns the following:
The MIN function is used to return the smallest value within a column. For example, you may want to get the lowest number of
employee of the month awards held by a single person. In
SQL, you would use the following query:
SELECT MIN(employee_month_awards) FROM employees;
The output for our query is:
If we wanted to get the highest number of
employee of the month awards held by a single person, we could use the MAX function instead:
SELECT MAX(employee_month_awards) FROM employees;
The output for our query is as follows:
Of note, you can use the
MAX functions on both numeric and alphabetic information in your database. If you wanted to get the name that appeared first in the alphabet from your database, you could use
MAX(name) could be used to get the name that comes last in the alphabet.
The AVG function returns the average value of a particular column. Let’s say we want to get the average number of
employee of the month awards held by each employee. We would use the following query to accomplish this goal:
SELECT AVG(employee_month_awards) FROM employees;
Our query returns:
You can also use the SUM function to find the total sum of a particular column. For example, if you wanted to find out how many
employee of the month awards have ever been issued, you could execute this query:
SELECT SUM(employee_month_awards) FROM employees;
Our query returns the following:
Aggregate functions have a number of uses in
SQL. If you’re looking to find the highest value in a dataset, the lowest value in a dataset, the average value of a column, the sum of a column, or the number of records that meet a certain condition, you’ll need to use aggregate functions.
In this tutorial, we have broken down the basics of queries in
SQL and how you can leverage aggregate functions to derive more insights from your data. Selecting distinct sets of data in
SQL is essential, and with this knowledge you are one step closer to being an