Excerpt
In SQL, aggregate functions let you perform a calculation on multiple data and return a single value. That’s why they are called “aggregate” functions.
Those aggregate functions are AVG(), COUNT(), SUM(), MIN(), and MAX().
While making queries with the aggregate functions, you can also use them in combination with the GROUP BY clause and HAVING statement in any relational database – MySQL PostgreSQL, and others.
In this article, you will learn how to use aggregate functions on their own and with the GROUP BY clause and HAVING statement.
## What We'll Cover
- Conclusion
ADVERTISEMENT
## How to Use Aggregate Functions
To show you how the aggregate functions work, I’ll be working with an employees table in an employees_data database.
Running SELECT * FROM employees got me the following:
, COUNT(), SUM(), MIN(), and MAX().
While making queries with the aggregate functions, you can also use them in combination with the GROUP BY clause and HAVING statement in any relational database – MySQL PostgreSQL, and others.
In this article, you will learn how to use aggregate functions on their own and with the GROUP BY clause and HAVING statement.
## What We'll Cover
- Conclusion
ADVERTISEMENT
## How to Use Aggregate Functions
To show you how the aggregate functions work, I’ll be working with an employees table in an employees_data database.
Running SELECT * FROM employees got me the following:
## Syntax of Aggregate Functions
The syntax for working with aggregate functions looks like this:
```plain text
aggregate_function(MODIFIER | expression)
```
- the aggregate function could be AVG, COUNT, MAX, MIN, or SUM
- the modifier could be all the values or the values in a particular column
This syntax would make more sense in practice, so let’s get to use it with the aggregate functions.
## How to Use the AVG() Aggregate Function
The AVG() aggregate function gets the total number of data and calculates their average.
I was able to get the average wage paid to the employees this way:
```plain text
SELECT AVG(wage)
FROM employees
```
The query below gets the average wage of junior developers:
```plain text
SELECT AVG(wage)
FROM employees
WHERE role = "Junior dev"
```
ADVERTISEMENT
### How to use the AVG() Function with GROUP BY and HAVING
You can get the average number of entries (rows) in a particular column with the GROUP BY clause and HAVING statement. This means you have to combine those two with AVG().
For instance, I was able to get the average wage paid to employees in each row with this query:
```plain text
SELECT role, AVG(wage)
FROM employees
GROUP BY role
```
I was also able to get the average wage of senior developers by using the HAVING statement:
```plain text
SELECT role, AVG(wage)
FROM employees
GROUP BY role
HAVING role = "Senior dev"
```
## How to Use the COUNT() Aggregate Function
COUNT() returns the number of rows in a table based on the condition (or filter) you apply.
For example, to get the total number of rows, I ran the query below:
```plain text
SELECT COUNT(*)
FROM employees
```
And I got 20:
To get the total number of employees from the USA, I ran the query below:
```plain text
SELECT COUNT(*)
FROM employees
WHERE country = "USA"
```
And to get the employees who are technical writers, I did this:
```plain text
SELECT COUNT(*)
FROM employees
WHERE role = "Tech Writer"
```
### How to Use COUNT() with GROUP BY and HAVING
In a large database, you can use the GROUP BY clause and HAVING statement in combination with COUNT() to get the total number of entries (rows) in a particular column.
In the database I’m using in this article, I was able to get the total number of employees in each row with the GROUP BY clause:
```plain text
SELECT role, COUNT(*)
FROM employees
GROUP BY role
```
To get the number of only the employees that are senior developers, I attached HAVING role = "Senior dev" to the query:
```plain text
SELECT role, COUNT(*)
FROM employees
GROUP BY role
HAVING role = "Senior dev"
```
ADVERTISEMENT
## How to Use the MAX() Aggregate Function
The MAX() function returns the maximum value within non-NULL values. This means it would ignore fields that are empty and return the highest value within those that are not empty.
For example, to get the highest wage in the employees table, I used the MAX() function like this:
```plain text
SELECT MAX(wage)
FROM employees
```
To get the maximum wage for mid-level developers, I used the WHERE statement:
```plain text
SELECT MAX(wage)
FROM employees
WHERE role = "Mid level dev"
```
### How to Use MAX() with GROUP BY and HAVING
To get the maximum wage in each role, the GROUP BY clause comes in handy:
```plain text
SELECT role, MAX(wage)
FROM employees
GROUP BY role
```
And to get the maximum wage in a particular role, combining the HAVING statement with the GROUP BY clause gets it done:
```plain text
SELECT role, MAX(wage)
FROM employees
GROUP BY role
HAVING role = "Tech writer"
```
## How to Use the MIN() Aggregate Function
The MIN() function is the opposite of the MAX() function – it returns the minimum value within non-NULL values.
For example, I got the lowest wage on the employees table this way:
```plain text
SELECT MIN(wage)
FROM employees
```
ADVERTISEMENT
### How to Use MIN() with GROUP BY and HAVING
Again, to get the minimum wage in each role, the GROUP BY clause can get it done:
```plain text
SELECT role, MIN(wage)
FROM employees
GROUP BY role
```
And to get the minimum wage of a particular role, the HAVING statement and GROUP BY clause are what to use:
```plain text
SELECT role, MIN(wage)
FROM employees
GROUP BY role
HAVING role = "Junior dev"
```
## How to Use the SUM() Aggregate Function
The SUM() aggregate function adds the number of entries in a column based on the filter applied.
The query below gets the total number of wages paid to employees:
```plain text
SELECT SUM(wage)
FROM employees
```
### How to Use SUM() with GROUP BY and HAVING
To get the sum of the total wages paid for employees in each role, I selected the role, used SUM() on the wages, and grouped them by the role:
```plain text
SELECT role, SUM(wage)
FROM employees
GROUP BY role
```
To get the total wages paid to technical writers only, I used the HAVING statement:
```plain text
SELECT role, SUM(wage)
FROM employees
GROUP BY role
HAVING role = "Tech Writer"
```
ADVERTISEMENT
## Conclusion
This article took you through what aggregate functions are in SQL, their syntax, and how to use them.
In addition, you also learned how to use aggregate functions with the GROUP BY clause, HAVING, and WHERE statements.
If you want to learn how the HAVING statement works, you should read this article I wrote on it.
Thank you for reading.
If you read this far, tweet to the author to show them you care.
Learn to code for free. freeCodeCamp's open source curriculum has helped more than 40,000 people get jobs as developers. Get started
ADVERTISEMENT