# PostgreSQL Aggregate Functions With Practical Examples

> To perform calculations on a set of rows, the aggregate functions are used. These functions perform calculations on the table rows and return only a single row…

**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()**](<https://commandprompt.com/education/postgresql-sum-function-with-practical-example/>) 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()**](<https://commandprompt.com/education/how-to-use-count-function-in-postgresql/>) 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()**](<https://commandprompt.com/education/how-to-use-avg-function-in-postgresql/>) 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()**](<https://commandprompt.com/education/how-to-use-max-function-in-postgresql/>) 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()](<https://commandprompt.com/education/how-to-use-min-function-in-postgresql/>) 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()**](<https://www.commandprompt.com/education/postgresql-string_agg-function-with-examples/>)” 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()](<https://www.commandprompt.com/education/postgresql-array_agg-function-with-examples/>) function.

##  **PostgreSQL JSON_AGG() Function**

The [JSON_AGG()](<https://www.commandprompt.com/education/postgresql-json_agg-function-by-practical-examples/>) 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.

---
[View this page online](https://www.commandprompt.com/education/postgresql-aggregate-functions-with-practical-examples/)