PostgreSQL SUM for ObjectRocket Instance

Introduction

This tutorial will cover how to use the PostgreSQL SUM for an ObjectRocket instance. The PostgreSQL SUM function obtains the sum, or total, of values and can be used in conjunction with other statements, such as the SUM with GROUP BY clause, to obtain specific data. However, is is important to note, the SUM function will ignore NULL values and those values will not be taken into account when executing the SUM function.

Prerequisites

• An instance of PostgreSQL is required on an ObjectRocket account to use the PostgreSQL SUM for an ObjectRocket instance function.

• A remote connection to the instance of PostgreSQL is required.

ObjectRocket Instance of PostgreSQL

Begin by setting up a PostgreSQL instance on a ObjectRocket account via the create instance tab.

Connection of PostgreSQL to the Instance

For Windows based machines, use either the command prompt or execute the following command to connect to the instance using the SQL (psql) shell:

For UNIX-based operating systems, such as macOS or Linux, execute the following command to connect to the instance using the UNIX terminal window:

PostgreSQL SUM Function

The SUM function is used to get the sum of all the values in a column that are a non-NULL value.

Following is the SUM function syntax:

A breakdown of the above syntax is as follows:

• Specifying ALL will obtain the sum of all of the non-NULL values from the column. This is the default for the SUM function and is optional.
• Specifying DISTINCT will obtain all of the distinct non-NULL values. This is also optional.
• The ‘expression’ is required and the values will be the sum.

PostgreSQL SUM Function Examples

A table, with records, must be created to use in the examples in this tutorial for the PostgreSQL SUM for an ObjectRocket instance.

Execute the following command to create a table named 'orders':

Next, insert the following records into the table:

Now the table is ready for use in the examples.

SUM of the Values

Executing the following SUM function statement will obtain the total quantity of the items and the total price in the 'orders' table.

SUM With GROUP BY

The GROUP BY clause is used to get the total quantity and the total price on each item. Following is an example:

SUM All

The following example demonstrates that specifying the keyword ALL will obtain the sum of all the values in the column:

Note that the WITH query has been used in the above example. This allows for the uses the sum of the price of each item on the SUM function.

SUM Distinct

As the total price of items '2' and '3' in the above example are the same, the DISTINCT clause will calculate the total price of these items and the total price of item '1'. This is because the DISTINCT clause only calculates distinct values. Execute the following statement for an example:

Conclusion

This tutorial explained how to use the PostgreSQL SUM for an ObjectRocket instance. The tutorial first provided instructions for setting up a PostgreSQL instance on a ObjectRocket account and then explained how to facilitate a connection of PostgreSQL to the instance on both Windows- and UNIX-based operating systems using the command prompt or the SQL (psql) shell. The article then covered how to execute the SUM function and provided a breakdown of the SUM function syntax. The tutorial then provided instructions, with examples, for creating tables and inserting records and then executing the SUM, the SUM with GROUP BY, the SUM ALL and the SUM DISTINCT statements. Remember that he SUM function is used to get the sum of all the values in a column that are of a non-NULL value.