Overview
In this article we will go through the various in built aggregate functions available in PostgreSQL. Aggregate functions perform a calculation on a set of rows and return a single value.
COUNT Function
The COUNT function is a simple and very useful function in counting the number of records, which are expected to be returned by a select statement.
Basic Syntax:
Select count(*) from table_name where condition
The where clause is optional in the query.
Example
Let us study the following examples to understand the behavior of the COUNT function.
First, we ran the count(*) function to get the numerical count of the records for the specified condition and then in order to support the theory, executed the 'select *' clause to return the records to tally their count.
To explain the rest of the aggregate functions we will use the below 'stocks' table:
MAX Function
PostgreSQL MAX function is used to find out the record with maximum value among a record set.
Basic Syntax:
SELECT MAX(column_name) FROM table_name;
Example 1
SELECT MAX(stock_price) from stocks;
The above given PostgreSQL statement finds out the stock having the maximum price
Example 2
We can find all the records with maximum value for each stock_genre using the GROUP BY clause as follows. Lets say we want to find the technology stock having the highest price.
SELECT stock_genre,MAX(stock_price) from stocks group by stock_genre;
The above given PostgreSQL query returns the price of the stock 'tcs' which is the highest in technology category.
MIN Function
PostgreSQL MIN function is used to find out the record with minimum value among a record set.
Basic Syntax:
SELECT MIN(column_name) FROM table_name;
Example 1
SELECT MIN(stock_price) from stocks;
The above given PostgreSQL statement finds out the stock having the minimum price
Example 2
We can find all the records with minimum value for each stock_genre using the GROUP BY clause as follows. Lets say we want to find the technology stock having the lowest price.
SELECT stock_genre,MIN(stock_price) from stocks group by stock_genre;
The above given PostgreSQL query returns the price of the stock 'infosys' which is the lowest in technology category.
SUM Function
The PostgreSQL SUM function is used to find out the sum of a column in a table.
Basic Syntax:
SELECT SUM(column_name) FROM table_name;
Example 1
SELECT SUM(stock_price) as net_stocks_value FROM stocks;
The above given PostgreSQL query returns the sum of all the stocks available in the table 'stocks'.
Example 2
We can find all the summation of various records using the GROUP BY clause as follows. Lets say we want to find the summation of the stock prices with respect to every stock_genre.
SELECT SUM(stock_price),stock_genre as net_stocks_value FROM stocks group by stock_genre;
The above given PostgreSQL query returns the sum of all the stocks available with respect to every stock_genre. The 'technology' had 2 stocks, the query returns the sum total of both.
ARRAY Function
The PostgreSQL ARRAY_AGG function is used to concatenate the input values including null into an array.
Basic Syntax:
SELECT ARRAY_AGG(column_name) FROM table_name;
Example
SELECT ARRAY_AGG(stock_name) FROM stocks;
Use the PostgreSQL ARRAY_AGG() function to return an array from a set of input values.
AVG Function
The PostgreSQL AVG function is one of the most commonly used aggregate functions in PostgreSQL. The AVG function is used to calculate the average value of a set.
Basic Syntax:
SELECT AVG(column_name) FROM table_name;
Example
SELECT AVG(stock_price) as avg_stocks_value FROM stocks;
The above given PostgreSQL query returns the average of all the stocks available in the table.
Conclusion
In this article we learn how to use the PostgreSQL aggregate functions such as AVG(), COUNT(), MIN(), MAX(), SUM() and ARRAY_AGG(). In practice, we often use the aggregate functions with the GROUP_BY clause in the SELECT statement as seen in the above examples. Hope this article will help to you started on your journey with PostgreSQL functions.
