PostgreSQL Tutorial: Aggregate Functions

September 27, 2024

Summary: in this tutorial, you will learn how to use the PostgreSQL aggregate functions such as AVG(), COUNT(), MIN(), MAX(), and SUM().

Table of Contents

Introduction to PostgreSQL aggregate functions

Aggregate functions perform a calculation on a set of rows and return a single row. PostgreSQL provides all standard SQL’s aggregate functions as follows:

  • ARRAY_AGG() – return an array from a set of input values.
  • AVG() – return the average value.
  • COUNT() – return the number of values.
  • MAX() – return the maximum value.
  • MIN() – return the minimum value.
  • STRING_AGG() – concatenate strings and place a separator between them.
  • SUM() – return the sum of all or distinct values.

We often use the aggregate functions with the GROUP BY clause in the SELECT statement. In these cases, the GROUP BY clause divides the result set into groups of rows and the aggregate functions perform a calculation on each group e.g., maximum, minimum, average, etc.

In addition, you can create custom aggregate functions in PostgreSQL.

You can use aggregate functions as expressions only in the following clauses:

PostgreSQL aggregate functions examples

Let’s use the film table in the sample database for the demonstration.

Film table

AVG() function examples

The following statement uses the AVG() function to calculate the average replacement cost of all films:

SELECT
	ROUND( AVG( replacement_cost ), 2 ) avg_replacement_cost
FROM
	film;

The following is the result:

 avg_replacement_cost
----------------------
                19.98
(1 row)

Noted that the ROUND() function was used to round the result to 2 decimal places.

To calculate the average replacement cost of the Drama films whose category id is 7, you use the following statement:

SELECT
	ROUND( AVG( replacement_cost ), 2 ) avg_replacement_cost
FROM
	film
INNER JOIN film_category USING(film_id)
INNER JOIN category USING(category_id)
WHERE
	category_id = 7;

Here is the result:

 avg_replacement_cost
----------------------
                21.09
(1 row)

COUNT() function examples

To get the number of films, you use the COUNT(*) function as follows:

SELECT 
    COUNT(*) 
FROM 
    film;

Here is the output:

 count
-------
  1000
(1 row)

To get the number of drama films, you use the following statement:

SELECT
	COUNT(*) drama_films
FROM
	film
INNER JOIN film_category USING(film_id)
INNER JOIN category USING(category_id)
WHERE
	category_id = 7;

The result showed that we have 62 drama films:

 drama_films
-------------
          62
(1 row)

MAX() function examples

The following statement returns the maximum replacement cost of films.

SELECT 
    MAX(replacement_cost)
FROM 
    film;

Output:

  max
-------
 29.99
(1 row)

To get the films that have the maximum replacement cost, you use the following query:

SELECT
	film_id,
	title
FROM
	film
WHERE
	replacement_cost =(
		SELECT
			MAX( replacement_cost )
		FROM
			film
	)
ORDER BY
	title;

Output:

 film_id |          title
---------+-------------------------
      34 | Arabia Dogma
      52 | Ballroom Mockingbird
      81 | Blindness Gun
      85 | Bonnie Holocaust
     138 | Chariots Conspiracy
...

The subquery returned the maximum replacement cost which then was used by the outer query for retrieving the film’s information.

MIN() function examples

The following example uses the MIN() function to return the minimum replacement cost of films:

SELECT 
    MIN(replacement_cost)
FROM 
    film;

Output:

 min
------
 9.99
(1 row)

To get the films which have the minimum replacement cost, you use the following query:

SELECT
	film_id,
	title
FROM
	film
WHERE
	replacement_cost =(
		SELECT
			MIN( replacement_cost )
		FROM
			film
	)
ORDER BY
	title;

Output:

 film_id |         title
---------+------------------------
      23 | Anaconda Confessions
     150 | Cider Desire
     182 | Control Anthem
     203 | Daisy Menagerie
...

SUM() function examples

The following statement uses the SUM() function to calculate the total length of films grouped by film’s rating:

SELECT
	rating,
	SUM( rental_duration )
FROM
	film
GROUP BY
	rating
ORDER BY
	rating;

The following picture illustrates the result:

 rating | sum
--------+------
 G      |  861
 PG     |  986
 PG-13  | 1127
 R      |  931
 NC-17  | 1080
(5 rows)

In this tutorial, you have learned about PostgreSQL aggregate functions and apply them to summarize data.

Search

Categories

Tags