SQL String Aggregate Functions

If you have ever dealt with SQL databases, you are probably familiar with aggregate functions. They are essentially a set of functions that perform the calculations on a set of values and return a single result.

An example includes functions like SUM(), AVG(), COUNT(), MAX, and MIN(). However, one thing you will notice about aggregate functions in SQL is that they are geared towards numerical operations.

But did you know that there are aggregate functions that deal with string values? In this tutorial, we will look at these functions, how they work, and how we can use them in our databases.

NOTE: It is a good note that most of the functions that are discussed in this post are not part of the standard SQL. As a result, they are an extension of various database engines such as PostgreSQL, SQL Server, etc.

What Are String Aggregate Functions?

String aggregate functions are a set of functions that perform the aggregation operations on a set of strings within a group or rows and return a single result.

We mainly use these functions with the GROUP BY clause to group the rows based on a certain column and then aggregate the strings in each group.

Some common string aggregate functions include:

GROUP_CONCAT() – PostgreSQL and MySQL
STRING_AGG – SQL Server
ARRAY_AGG – PostgreSQL
LISTAGG – Oracle

Let us explore each function and what it provides.

GROUP_CONCAT()

The GROUP_CONCAT() function is supported by PostgreSQL and MySQL databases. It allows us to concatenate the values from multiple rows into a single string.

Suppose we have an “employees” table as follows:

CREATE TABLE employees (
employee_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department VARCHAR(50)
);

INSERT INTO employees (first_name, last_name, department) VALUES
('Alice', 'Smith', 'Human Resources'),
('Bob', 'Johnson', 'Marketing'),
('Charlie', 'Wilson', 'Finance'),
('David', 'Brown', 'Sales'),
('Eva', 'Davis', 'Engineering');

Output:

If we want to determine the full name of the employee by taking the first name and concatenating with the last name, we can use the GROUP_CONCAT() function as demonstrated in the following example:

SELECT
department,
GROUP_CONCAT(first_name, ' ', last_name) AS full_name
FROM
employees
GROUP BY
department;

In this case, we use the function to concatenate the strings from the “first_name” and “last_name” columns, and an empty string to add a space to the names.

This contains a list of employees in each department.

STRING_AGG

This function is similar to the GROUP_CONCAT() function but it is only supported in the SQL Server database.

An example usage is as follows:

SELECT
department,
STRING_AGG(first_name, last_name, ' ') AS full_name
FROM
employees
GROUP BY
department;

This performs the string concatenation on the specified columns as the space as a delimiter.

ARRAY_AGG

The ARRAY_AGG function is only available in the PostgreSQL database. It allows us to aggregate the values into a single array.

An example is as follows:

SELECT
department,
ARRAY_AGG(first_name) AS employees_list
FROM
employees
GROUP BY
department;

This should aggregate the employee name as an array.

LISTAGG()

The LISTAGG() function is only available in the Oracle database. It allows us to concatenate the values into a single string with a specified delimiter.

An example is as follows:

SELECT department, LISTAGG(first_name, last_name, ' ') WITHIN GROUP (ORDER BY employee_name ASC) AS employees
FROM employees
GROUP BY department;

This concatenates the names of the employees and the specified delimiter.

Conclusion

In this tutorial, we learned about the string aggregate functions that allows us to provide a list of string values and perform an operation into a single resulting string value.