**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:

**PostgreSQL SUM() Function.****PostgreSQL COUNT() Function.****PostgreSQL AVG() Function.****PostgreSQL MAX() Function.****PostgreSQL MIN() Function.****PostgreSQL STRING_AGG() Function.****PostgreSQL ARRAY_AGG() Function.****PostgreSQL JSON_AGG() Function.****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.