Using Aggregate Functions (COUNT, SUM, AVG, MIN, MAX) in PostgreSQL

Introduction

Aggregate functions in PostgreSQL are powerful tools that allow you to perform calculations on a set of rows and return a single result. These functions are widely used in database operations such as reporting, analytics, and data summarization. This article explores five commonly used aggregate functions in PostgreSQL: COUNT, SUM, AVG, MIN, and MAX.

Understanding Aggregate Functions in PostgreSQL

1. COUNT() – Counting Rows

The COUNT() function returns the number of rows that match a specified condition.

Syntax:

SELECT COUNT(column_name) FROM table_name WHERE condition;

Example:

carrentaldb=# SELECT * FROM reservations WHERE status = 'Confirmed';
 reservation_id | customer_id | car_id | employee_id | pickup_date | return_date | total_rental_price |  status   
----------------+-------------+--------+-------------+-------------+-------------+--------------------+-----------
              1 |           1 |      2 |           1 | 2025-02-01  | 2025-02-05  |             180.00 | Confirmed
              5 |           5 |      4 |           5 | 2025-05-10  | 2025-05-14  |             200.00 | Confirmed
              8 |           8 |      9 |           8 | 2025-08-15  | 2025-08-17  |             240.00 | Confirmed
(3 rows)

carrentaldb=# SELECT COUNT(*) FROM reservations WHERE status = 'Confirmed';
 count 
-------
     3
(1 row)

This query counts the number of completed orders in the orders table.

2. SUM() – Calculating the Total Sum

The SUM() function calculates the total sum of a numeric column.
Syntax:

SELECT SUM(column_name) FROM table_name WHERE condition;

Example:

carrentaldb=# SELECT total_rental_price FROM reservations WHERE pickup_date >= '2025-05-01';
 total_rental_price 
--------------------
             200.00
             190.00
              78.00
             240.00
             500.00
             175.00
(6 rows)

carrentaldb=# SELECT SUM(total_rental_price) FROM reservations WHERE pickup_date >= '2025-05-01';
   sum   
---------
 1383.00
(1 row)

This query calculates the total payments made since January 1, 2024.

3. AVG() – Calculating the Average

The AVG() function returns the average value of a numeric column.

Syntax:

SELECT AVG(column_name) FROM table_name WHERE condition;

Example:

carrentaldb=# carrentaldb=# SELECT total_rental_price FROM reservations WHERE pickup_date >= '2025-05-01';
 total_rental_price 
--------------------
             200.00
             190.00
              78.00
             240.00
             500.00
             175.00
(6 rows)

SELECT avg(total_rental_price) FROM reservations WHERE pickup_date >= '2025-05-01';
         avg          
----------------------
 230.5000000000000000
(1 row)

This query finds the average salary of employees in the HR department.

4. MIN() – Finding the Minimum Value

The MIN() function returns the smallest value in a column.

Syntax:

SELECT MIN(column_name) FROM table_name WHERE condition;

Example:

carrentaldb=# SELECT total_rental_price FROM reservations WHERE pickup_date >= '2025-05-01';
 total_rental_price 
--------------------
             200.00
             190.00
              78.00
             240.00
             500.00
             175.00
(6 rows)

carrentaldb=# SELECT min(total_rental_price) FROM reservations WHERE pickup_date >= '2025-05-01';
  min  
-------
 78.00
(1 row)

This query retrieves the lowest product price in the Electronics category.

5. MAX() – Finding the Maximum Value

The MAX() function returns the highest value in a column.

Syntax:

SELECT MAX(column_name) FROM table_name WHERE condition;

Example:

carrentaldb=# SELECT total_rental_price FROM reservations WHERE pickup_date >= '2025-05-01';
 total_rental_price 
--------------------
             200.00
             190.00
              78.00
             240.00
             500.00
             175.00
(6 rows)

carrentaldb=# SELECT max(total_rental_price) FROM reservations WHERE pickup_date >= '2025-05-01';
  max   
--------
 500.00
(1 row)

Conclusion

Aggregate functions in PostgreSQL are essential for data analysis and summarization. By using COUNT, SUM, AVG, MIN, and MAX, you can extract valuable insights from your database. Combining these functions with GROUP BY allows for more granular reporting and deeper analysis. Mastering these functions will enhance your ability to work with large datasets efficiently.