EDUCBA

Home Data Science Data Science Tutorials PostgreSQL Tutorial Aggregate Functions in PostgreSQL

Aggregate Functions in PostgreSQL

Priya Pedamkar
Article byPriya Pedamkar

Updated May 3, 2023

Aggregate Functions in PostgreSQL

Introduction to Aggregate Function in PostgreSQL

PostgreSQL aggregate functions are used to compute the set of input values in one result. It produced a single result for an entire group of tables. PostgreSQL aggregate functions are used to create a summarized set of results. They return results based on a group of rows set. Aggregate functions will treat all table rows as a group by default. Like the group by clause of the select statement, the statement divides all rows into smaller groups or chunks. Aggregate functions support aggregating multiple rows in a single dataset. We can see a list of aggregate functions using the \df command.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Various Aggregate Functions in PostgreSQL

Below is the list of aggregate functions in PostgreSQL as follows.

  • COUNT
  • SUM
  • MIN
  • MAX
  • AVG

Below is a detailed description of aggregate functions. Also, below is the syntax of aggregate functions. We have used the customer table as an example.

Customer table:

select * from Customer;

aggregate functions

select version();

aggregate functions

Syntax:

  • Aggregate function using the expression:
aggregate_name (expression [... , ] [order_by_clause] )
  • Using ALL as an aggregate function:
aggregate_name (ALL expression [,...] [ order_by_clause] )
  • Aggregate function using DISTINCT clause:
aggregate_name (DISTINCT expression [,...] [ order_by_clause] )
  • Using the asterisk (*), perform an aggregate function:
aggregate_name (*)

Below is the parameter description are as follows.

  • Aggregate name: Name of the aggregate function.
  • Expression: This is the value or value of a column in an aggregate function, which does not contain any aggregate expression.
  • Order by the clause: It is optional used to set to arrange results in a set of orders.

1. Count Aggregate Function

  • The count aggregate function in PostgreSQL returns the count of rows from a selected number of columns.

Syntax:

COUNT (* | DISTINCT ALL | Column_name)

Below is the parameter description of syntax are as follows.

  • Column name: Name of the column that we have used in the query to count the records’ values.
  • Asterisk (*): The Asterisk (*) indicates all the rows, it will return the count of rows of the column which have been used in a query.
  • DISTINCT: This clause is used to find unique values from the table; also, this parameter is optional.
  • ALL: This is the default clause of the count aggregate function also; this is optional.

Example:

1. If we want to get a total count of rows from the customer table.

testing=# select COUNT (cust_balance) from customer;

select COUNT

2. If we want to get a count of distinct rows for the customer balance column from the customer table.

testing=# select COUNT (distinct(cust_balance)) from customer;

distinct cust_balance

3. If we want to get the count of rows for the cust_id column from the customer table.

testing=# select count(cust_id) from customer;

cust_id

select count (*) from a customer;

aggregate functions

select count (1) from a customer;

aggregate functions

select count (cust_name) from customer;

aggregate functions

select count (distinct(cust_name)) from customer;

aggregate functions

2. SUM Aggregate Function

The sum aggregate function in PostgreSQL returns the sum of values from a selected number of columns. It will select a total of no numeric function and return a sum of all records.

Syntax:

SUM (* | DISTINCT ALL | Column_name)

Below is the parameter description of syntax are as follows.

  • Column name: Name of the column that we have used in the query to sum the records’ values.
  • Asterisk (*): The Asterisk (*) indicates all the rows, it will return the sum of columns that have been used in a query.
  • DISTINCT: This clause is used to find unique values from the table; also, this parameter is optional.
  • ALL: This is a default clause of the SUM aggregate function; also, this is optional.

Example:

1. If we want to calculate the total sum of customer balance from the customer table.

testing=# select SUM (cust_balance) from customer;

select SUM

2. If we want to calculate the distinct sum of customer balance from the customer table.

testing=# select SUM(distinct(cust_balance)) from customer;

calculate distinct sum

3. MIN Aggregate Function

The Min function returns the result of the smallest value of all selected values of the column. It will select the lowest value from a selected column.

Syntax:

MIN (* | [DISTINCT] ALL | Column_name)

Below is the parameter description of syntax are as follows.

  • Column name: Name of the column that we have used in the query.
  • Asterisk (*): The Asterisk (*) indicates all the rows, it will return all rows MIN value of tables.
  • DISTINCT: This clause is used to find the unique smallest value from the table.
  • ALL: This is the default clause of the MIN aggregate function also; this is optional.

Example:

1. If we want to calculate the minimum value of customer balance from the customer table.

testing=# select MIN (cust_balance) from customer;

calculate minimum value

2. If we want to calculate the distinct minimum value of customer balance from the customer table.

testing=# select MIN(distinct(cust_balance)) from customer;

distinct minimum value

select MIN(ALL(cust_balance)) from customer;

aggregate functions

4. MAX Aggregate Function

MAX function returns the result of the largest value of all selected values of the column. It will select the largest value from the selected column.

Syntax:

MAX (* | [DISTINCT] ALL | Column_name)

Below is the parameter description of syntax are as follows.

  • Column name: Name of column.
  • Asterisk (*): The Asterisk (*) indicates all the rows
  • DISTINCT: This clause is used to find the unique largest value from the table.
  • ALL: This is a default clause of the MAX aggregate function also; this is optional.

Example:

1. If we want to calculate the maximum customer balance from the customer table.

testing=# select MAX (cust_balance) from customer;

select MAX

2. If we want to calculate a distinct maximum customer balance from the customer table.

testing=# select MAX(distinct(cust_balance)) from customer;

select MAX(distinct(cust_balance)

select MAX(ALL(cust_balance)) from customer;

select MAX(ALL(cust_balance)

5. AVG Aggregate Function

AVG function returns the average of all selected values of the column.

Syntax:

AVG (* | [DISTINCT] ALL | Column_name)

Below is the parameter description of syntax are as follows.

  • Column name: Name of the column that we have used to calculate the average.
  • Asterisk (*): The Asterisk (*) indicates all the rows
  • DISTINCT: This clause is used to find a unique average value from the table.
  • ALL: This is a default clause of the AVERAGE aggregate function also; this is optional.

Example:

1. If we want to calculate the maximum customer balance from the customer table.

testing=# select AVG (cust_balance) from customer;

maximum customer balance

2. If we want to calculate a distinct maximum customer balance from the customer table.

testing=# select AVG(distinct(cust_balance)) from customer;

maximum customer balance 

select AVG(ALL(cust_balance)) from customer;

maximum customer balance

Conclusion

PostgreSQL aggregate function is handy for finding the result of tables. Mainly COUNT, MAX, MIN, AVG, and SUM functions are used in PostgreSQL. The aggregate function will support the aggregate no of columns in a table. The aggregate function will produce a single result for the entire group of tables.

Recommended Articles

We hope that this EDUCBA information on “Aggregate Functions in PostgreSQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. SQL HAVING Clause
  2. AND in SQL
  3. MySQL Aggregate Function
  4. MySQL BETWEEN
  5. PostgreSQL GROUP BY | Examples | How to Work?