Aggregate Functions in Postgres on ObjectRocket

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

When you’re querying your PostgreSQL database, using aggregate functions can help you group your result set in more meaningful ways. With the help of functions like SUM(), COUNT() and AVG(), you can gain better insights into your data. In this article, we’ll take a closer look at these functions and show you how to use aggregate functions in Postgres on ObjectRocket.

Prerequisites

Before moving forward with this tutorial, be sure that the following prerequisites have been met:

  • You’ll need to have PostgreSQL 11 installed.

  • You’ll need to create an instance of Postgres for your ObjectRocket account using the Mission Control panel.

  • You should have a basic understanding of SQL query syntax and how to use queries in PostgreSQL.

  • You should have a basic understanding of how aggregate functions work.

What Is an Aggregation?

Aggregation refers to any process that allows us to gather information and transform that information into summary form. This aggregated information can be used for various purposes like statistical analysis or data presentation.

Postgres provides a number of aggregate functions, such as AVG(), COUNT(), MAX(), MIN(), and SUM(). Let’s look at each one of these functions:

  • AVG(): This returns the average value of a numeric column.
  • COUNT(): This function allows us to retrieve the number of rows that matches a defined condition of a certain query.
  • MAX(): This aggregate function retrieves the maximum value within a result set.
  • MIN(): This aggregate function is the opposite of the MAX() function, returning the minimum value of a result set.
  • SUM(): This aggregate function returns the sum of all values or a set of values.

We can pair the above functions with the GROUP BY clause in a SELECT statement to further transform our result set into more readable and meaningful results.

Sample Data Set

We’ll be using the following sample data set in our examples:

1
2
3
4
5
6
7
8
9
10
11
12
13
 id | sku  |          name           |   category    | price
----+------+-------------------------+---------------+-------
  1 | 1021 | nvidia gtx-1660 ti      | graphic card  |   300
  2 | 1022 | tforce 16gb 3200mhz     | memory module |   250
  3 | 1023 | kingston 8gb 2000mhz    | memory module |   170
  4 | 1024 | ASUS ROG keyboard       | Keyboard      |   110
  5 | 1025 | nvidia gtx 2700         | graphic card  |   500
  6 | 1026 | Ryzen 7                 | processor     |   500
  7 | 1027 | Ryzen 8                 | processor     |   600
  8 | 1028 | de luxe mechanical keys | keyboard      |   200
  9 | 1029 | Intel core-i9           | processor     |   500
 10 | 1030 | viper 4gb 3200mhz       | memory module |   200
(10 rows)

Postgres AVG Function

The first aggregate function we’ll look at is the AVG() function. Shown below is the basic form of the function:

1
AVG(column_name)

In the following example, we want to get the average price of all existing rows in the hardware table:

1
SELECT AVG(price) FROM hardware;

This statement can be rewritten for better formatting using the to_char function, converting the result to a formatted string:

1
2
3
4
5
6
7
SELECT
    to_char(
        AVG (price),
        '99999999999999999D99'
    ) AS average_price
FROM
    hardware;

The output should look like this:

1
2
3
4
     average_price
-----------------------
                333.00
(1 row)

Postgres COUNT Function

Next, we’ll look at the Postgres COUNT() function. The basic format of this function is shown below:

1
2
3
4
5
6
SELECT
   COUNT(*)
FROM
   tableName
WHERE
   condition;

In this example, we’ll count the rows of the hardware table where the value of category equals memory module:

1
SELECT COUNT(*) FROM hardware WHERE category = 'memory module';

The output should look like this:

1
2
3
4
 count
-------
     3
(1 row)

Postgres MAX Function

In this section, we’ll show you how to use the Postgres MAX() function. The basic form of the MAX() function can be seen below:

1
MAX(column_name);

In the example shown below, we get the maximum value of price in the hardware table:

1
SELECT MAX(price) FROM hardware;

The output should be:

1
2
3
4
 max
-----
 600
(1 row)

We can see that the Ryzen 8 is indeed the most expensive item in the hardware table.

Postgres MIN Function

As we mentioned earlier, the MIN() function is the opposite of the MAX() function. This function retrieves the minimum value within the result set.

The basic form of the statement is shown below:

1
SELECT MIN(column_name) FROM TABLE_NAME;

Here’s a simple example where we retrieve the minimum value of price within the hardware table:

1
SELECT MIN(price) FROM hardware;

The output should look like this:

1
2
3
4
 min
-----
 110
(1 row)

Postgres SUM Function

The last function we’ll review is the Postgres SUM() function. The basic syntax for the function looks like the following:

1
SUM (COLUMN)

In this example, we find out how much it will cost if we were to buy all available graphic cards in the hardware table:

1
2
3
4
5
6
SELECT
    SUM (price) AS total_cost
FROM
    hardware
WHERE
    category = 'graphic card';

The output should look like the following:

1
2
3
4
 total_cost
------------
        800
(1 row)

Let’s use a SELECT statement to verify our results:

1
2
3
4
5
6
inventory=> SELECT * FROM hardware WHERE category = 'graphic card';
 id | sku  |        name        |   category   | price
----+------+--------------------+--------------+-------
  1 | 1021 | nvidia gtx-1660 ti | graphic card |   300
  5 | 1025 | nvidia gtx 2700    | graphic card |   500
(2 ROWS)

Conclusion

Aggregate functions can help you group the results of a PostgreSQL query and allow you to gain new insights into your data. In this article, we explained how to use various aggregate functions in Postgres on ObjectRocket. With our instructions to guide you, you’ll be able to use these functions in your own PostgreSQL queries.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.