SQL Aggregate Functions – How to GROUP BY in MySQL and PostgreSQL

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

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:

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:

SELECT AVG(wage) FROM employees

The query below gets the average wage of junior developers:

SELECT AVG(wage) FROM employeesWHERE role = "Junior dev"

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:

SELECT role, AVG(wage) FROM employeesGROUP BY role

I was also able to get the average wage of senior developers by using the HAVING statement:

SELECT role, AVG(wage) FROM employeesGROUP BY roleHAVING 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:

SELECT COUNT(*) FROM employees

And I got 20:

To get the total number of employees from the USA, I ran the query below:

SELECT COUNT(*) FROM employeesWHERE country = "USA"

And to get the employees who are technical writers, I did this:

SELECT COUNT(*) FROM employeesWHERE 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:

SELECT role, COUNT(*) FROM employeesGROUP BY role

To get the number of only the employees that are senior developers, I attached HAVING role = "Senior dev" to the query:

SELECT role, COUNT(*) FROM employeesGROUP BY roleHAVING role = "Senior dev"

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:

SELECT MAX(wage) FROM employees

To get the maximum wage for mid-level developers, I used the WHERE statement:

SELECT MAX(wage) FROM employeesWHERE 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:

SELECT role, MAX(wage) FROM employeesGROUP BY role

And to get the maximum wage in a particular role, combining the HAVING statement with the GROUP BY clause gets it done:

SELECT role, MAX(wage) FROM employeesGROUP BY roleHAVING 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:

SELECT MIN(wage) FROM employees

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:

SELECT role, MIN(wage) FROM employeesGROUP BY role

And to get the minimum wage of a particular role, the HAVING statement and GROUP BY clause are what to use:

SELECT role, MIN(wage) FROM employeesGROUP BY roleHAVING 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:

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:

SELECT role, SUM(wage) FROM employeesGROUP BY role

To get the total wages paid to technical writers only, I used the HAVING statement:

SELECT role, SUM(wage) FROM employeesGROUP BY roleHAVING role = "Tech Writer"

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.