Aggregation refers to the concept where a specific outcome is formed from the combination of several elements. In Postgres, aggregation is performed via different built-in methods, such as SUM(), AVG(), COUNT(), etc. All these methods serve a unique purpose, however, one thing is common in all of them i.e., accept multiple elements and return a single outcome.

The PostgreSQL aggregate functions allow us to compute/calculate a set of rows. These functions perform calculations on the table rows and return only a single row.

Quick Outline

Today, we will walk you through the following Postgres aggregate functions:

1. PostgreSQL SUM() Function.
2. PostgreSQL COUNT() Function.
3. PostgreSQL AVG() Function.
4. PostgreSQL MAX() Function.
5. PostgreSQL MIN() Function.
6. PostgreSQL STRING_AGG() Function.
7. PostgreSQL ARRAY_AGG() Function.
8. PostgreSQL JSON_AGG() Function.
9. PostgreSQL JSONB_AGG() Function.

This write-up will discuss each of the functions mentioned above through Practical examples. So, let's begin.

PostgreSQL SUM() Function

PostgreSQL provides a built-in SUM() function that is used to perform the addition on a set of values. The below snippet illustrates the syntax of the SUM() function:

SUM(exp);

Here, “exp” represents an expression.

Example: How to Use SUM() Function on Table’s Data?

Let’s say we have a table bike_details. We will execute the SELECT query to fetch all the records of the bike_details table:

SELECT * FROM bike_details;

Let’s perform the addition on the bike_price column using the SUM() Function:

SELECT SUM(bike_price) AS total_price
FROM bike_details;

The output authenticates that the SUM() function returns the sum of the bike_price column.

PostgreSQL COUNT() Function

The table rows can be counted using PostgreSQL's COUNT() function. In PostgreSQL, the COUNT() function is used to get all the rows, including duplicates and NULL. The below snippet illustrates the syntax of the Postgres COUNT(*) function:

SELECT COUNT(*) 
FROM tab_name;

The COUNT(*) function will fetch all the rows(including duplicates and NULL) from the targeted table based on the specified condition.

Example: How Does the COUNT(*) Function Work in PostgreSQL?

The bike_details table has some duplicated and null values. The following query will calculate the total number of rows in the bike_details table:

SELECT COUNT(*) 
FROM bike_details;

The output shows that the bike_details table has 10 rows. It proves that the COUNT(*) function counted all the rows, including the duplicates and null.

PostgreSQL AVG() Function

PostgreSQL provides a built-in AVG() function that is used to retrieve the average of a set. The below snippet demonstrates the basic syntax of the AVG() function:

AVG(col_name);

Let’s implement the AVG() function practically to get profound knowledge.

Example: How to Use AVG() Function on Table’s Data?

Let’s compute the average of the bike_price column using the AVG() Function:

SELECT AVG(bike_price)
FROM bike_details;

Let’s run the below query to get the average up to specific decimal places:

SELECT AVG(bike_price)::numeric(10, 3) 
FROM bike_details;

The above query will return the average of the bike_price column in an easily understandable format, i.e., the AVG() will return the result up to three decimal places:

Output proves the working of the AVG() function.

PostgreSQL MAX() Function

PostgreSQL offers a built-in MAX() function that is used to retrieve the maximum value of a set. The below snippet will show you the basic syntax of the MAX() function:

MAX(exp);

Here, exp represents an expression. Let’s practically implement the MAX() function to get profound knowledge about it.

Example: How to Use MAX() Function on Table’s Data?

Let’s find the bike with the maximum price using the MAX() Function:

SELECT MAX(bike_price)
FROM bike_details;

The output shows that in the bike_details table, the most expensive bike costs 150000.

PostgreSQL MIN() Function

PostgreSQL offers a built-in MIN() function that retrieves a set's minimum value. The below snippet will show you the basic syntax of the MIN() function:

MIN(exp);

Here, exp represents an expression. Let's implement it practically to understand the MIN() function in a better way.

Example: How to Use the MIN() Function in Postgres?

Let’s find the bike with the minimum price. To do that, we will use the Postgres MIN() Function:

SELECT MIN(bike_price)
FROM bike_details;

The output shows that the bike_details table's most cost-effective bike costs 80000.

PostgreSQL STRING_AGG() Function

STRING_AGG() is a well-known Postgres aggregate function that accepts input data as an argument, creates a concatenated string, and returns it as output. It concatenates/combines the string values using a separator or delimiter, as shown in the below syntax.

STRING_AGG ( exp, separator [ORDER BY ASC | DESC] )

Here, the “exp” can be a table’s column or an expression. The separator can be any valid separator/delimiter like a comma “,”, hyphen “-”, etc. Moreover, users can sort the aggregated data using the ORDER BY clause, however, it's optional(can be skipped).

Example: How Does STRING_AGG() Work in Postgres?

In this example, we will use the STRING_AGG() function to aggregate the bike numbers based on bike color:

SELECT bike_color, STRING_AGG(bike_number, ',')
 FROM bike_details 
 GROUP BY bike_color;

Here, the STRING_AGG() function combines all the bike_numbers using a comma, and the GROUP BY clause groups the bikes based on bike colors:

Visit the following dedicated guide on the “STRING_AGG()” function to learn more about it with proper examples.

PostgreSQL ARRAY_AGG() Function

ARRAY_AGG() is an aggregate function that helps us group/aggregate the data in an array. To do that, it accepts an expression (which can be an expression or a table’s column), aggregates the given data, and retrieves an array of given data. The data type of the retrieved array will be the same as of given data.

ARRAY_AGG(exp, [ORDER BY [sort_exp | column_name {ASC | DESC}], [....]);

Users can use the ORDER BY clause to sort the aggregated data using the, however, it's optional and can be skipped.

Example: How Does ARRAY_AGG() Work in Postgres?

Execute the provided code to aggregate the values of the bike_model column of the bike_details table using the ARRAY_AGG() function:

SELECT ARRAY_AGG(bike_model)
FROM bike_details;

The ARRAY_AGG() function successfully aggregates the values of the bike_model column in an integer-type array (since the type of the provided column was INTEGER).

You can explore more use cases of the stated function by reading our dedicated guide on the ARRAY_AGG() function.

PostgreSQL JSON_AGG() Function

The JSON_AGG() function accepts an expression/column, aggregates them, and retrieves a single JSON array. The return type of this function is JSON:

JSON_AGG(exp)

Here, “exp” can be any valid expression or a table column.

Example: How Does JSON_AGG() Work in Postgres?

In the following code, the JSON_AGG() function is implemented on “bike_number” column of the “bike_details” table:

SELECT JSON_AGG(bike_number)
FROM   bike_details;

All bike numbers including null values are aggregated into a single array whose data type is “JSON”:

PostgreSQL JSONB_AGG() Function

The JSONB_AGG() function is also an aggregate function that works similarly to JSON_AGG(). The only difference is it retrieves a JSONB array instead of a JSON array.

JSONB_AGG(exp);

The return type of this function is JSONB:

Example: How Does JSONB_AGG() Work in Postgres?

Let’s implement the JSONB_AGG() function on the same code to see how it works in Postgres:

SELECT JSONB_AGG (bike_number)
FROM bike_details;

From the output, you can observe that the stated function has returned a JSONB array:

That’s all about Postgres aggregate functions.

Conclusion

In PostgreSQL, we can perform computations/calculations on a set of rows using Postgres aggregate functions. These functions perform calculations on the table rows and return only a single row. This post has explained the working of several aggregate functions, including SUM(), COUNT(), AVG(), MAX(), MIN(), ARRAY_AGG(), etc., using practical examples.