How to Use the Postgres AVG on an ObjectRocket instance

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

Introduction

The Postgres AVG function is a commonly used aggregate function in PostgreSQL. It is used to determine the average of all of the selected values in a column of a field of records, typically calculating the average value of a numeric column. The PostgreSQL AVG function can calculate the average of records when used in conjunction with the GROUP BY clause, such as obtaining the average height, weight or salary of a specified group people. This tutorial will cover how to use the Postgres AVG on an ObjectRocket instance.

Prerequisites

  • The Postgres software must be properly installed and configured on the local device or system to use the Postgres AVG on an ObjectRocket instance. The software can be download here: PostgreSQL 11

  • An instance of Postgres for an ObjectRocket’s account must be set up via the Mission Control panel, shown here:

Image from Gyazo

What is the PostgreSQL AVG() function

As the name implies, the Postgres AVG() function is used to return the average value against a set of numeric fields or a column.

Here is the syntax for the basic AVG() function:

1
AVG(COLUMN)

It is important to note that the AVG() function is often used in conjunction with the SELECT and HAVING clauses.

Connecting to a PostgreSQL instance on ObjectRocket

This section will provide the details for the Postgres connection.

Connecting using Postgres psql

Begin by executing the following psql command from PostgreSQL:

1
2
3
4
5
Server [localhost]: ingress.w98sujpz.launchpad.objectrocket.cloud
DATABASE [postgres]: <your_database_name>
Port [5432]: 4149
Username [postgres]: pguser
Password FOR USER orkb: <your_password>

Connecting using Terminal

Now execute the following command in the Postgres terminal to connect to the PostgreSQL instance on ObjectRocket:

1
psql -h ingress.w98sujpz.launchpad.objectrocket.cloud -U pguser -d postgres -p 4149

Sample Data Set

Following is a sample data set that will be used in the examples in this tutorial:

1
2
3
4
5
6
7
8
9
10
11
12
13
 id |          menu_item           | price
----+------------------------------+-------
  1 | yum burger(solo)             | 33.00
  2 | yum burger w/ drink          | 60.00
  3 | yum burger value meal        | 79.00
  4 | yum burger cheese (solo)     | 44.00
  5 | yum burger cheese w/ drink   | 70.00
  6 | yum burger cheese value meal | 90.00
  7 | yum burger TLC (solo)        | 64.00
  8 | yum burger TLC w/ drink      | 89.00
  9 | yum burger TLC value meal    | 99.00
 10 | bacon cheese yumburger       | 99.00
(10 rows)

Postgres AVG() function Example

The following example shows how to obtain the average amount of the prices of the items on a menu:

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

Notice that the to_char function was used in the above example to return the following formatted string:

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

Postgres AVG() function with SUM() function example

This next example demonstrates how to pair the AVG() function with the SUM() function to obtain the average price of all of the menu items from the total price of all menu items:

1
2
3
4
5
6
7
8
9
10
11
SELECT
    TO_CHAR(
        AVG (price),
        'FM999999999.00'
    ) AS "Average Price",
    TO_CHAR(
        SUM (price),
        'FM999999999.00'
    ) AS "Total Price"
FROM
    burgers;

The output should resemble the following:

1
2
3
4
 Average Price | Total Price
---------------+-------------
 72.70         | 727.00
(1 row)

Postgres AVG() function with GROUP BY clause

This last example will show how using the AVG() function in conjunction with the GROUP BY clause will further refine the result.

Here the average price for every menu item is grouped by price:

1
2
3
4
5
6
SELECT
        menu_item, AVG(price)
    FROM
        burgers
    GROUP BY
        price;

The result should resemble the following:

1
2
3
4
5
6
7
8
9
10
11
12
        avg
---------------------
 64.0000000000000000
 60.0000000000000000
 79.0000000000000000
 44.0000000000000000
 70.0000000000000000
 89.0000000000000000
 99.0000000000000000
 90.0000000000000000
 33.0000000000000000
(9 rows)

Notice there are only nine rows returned in the above example. This is because there were two menu items that had the same average price.

Conclusion

This tutorial explained how to use the Postgres AVG on an ObjectRocket instance. The tutorial first explained and provided the syntax for the PostgreSQL AVG function. The article then covered how to connect to a PostgreSQL instance on ObjectRocket using Postgres and the Postgres terminal. The tutorial then provided a sample data set for use in the examples and provided working examples on how to use the Postgres AVG() function in conjunction with the SUM() function and GROUP BY clauses. Remember that if multiple rows in a group contain items that have the same average price, or identical values, then the items will be counted, and return as a single row when using the GROUP BY clause. Also bear in mind that an instance of Postgres for an ObjectRocket’s account must be set up to use the Postgres AVG on an ObjectRocket instance.

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.