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:
1 2 3 4 5 | Server [localhost]: ingress.w98sujpz.launchpad.objectrocket.cloud DATABASE [PostgreSQL]: Port [5432]: 4144 Username [PostgreSQL]: orkb Password FOR USER orkb: |
For UNIX-based operating systems, such as macOS or Linux, execute the following command to connect to the instance using the UNIX terminal window:
1 2 3 4 | psql -h ingress.w98sujpz.launchpad.objectrocket.cloud \ -U orkb \ -d PostgreSQL \ -p 4144 |
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:
1 | SELECT SUM( [ALL | DISTINCT] expression ); |
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 theSUM
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'
:
1 2 3 4 5 | CREATE TABLE orders ( item_id INT, quantity INT, price NUMERIC ); |
Next, insert the following records into the table:
1 2 3 4 5 6 7 | INSERT INTO orders VALUES ('1', '10', '23.5'), ('2', '8', '32.25'), ('2', '15', '15.89'), ('3', '12', NULL), ('3', '7', '20.35'), ('3', '9', '27.79'); |
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.
1 2 3 4 5 6 7 8 | SELECT SUM(quantity) total_quantity, SUM(price) total_price FROM orders; total_quantity | total_price ----------------+------------- 61 | 119.78 (1 ROW) |
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:
1 2 3 4 5 6 7 8 9 10 11 12 | SELECT item_id, SUM(quantity) total_quantity, SUM(price) total_price FROM orders GROUP BY item_id; item_id | total_quantity | total_price ---------+----------------+------------- 3 | 28 | 48.14 2 | 23 | 48.14 1 | 10 | 23.5 (3 ROWS) |
SUM All
The following example demonstrates that specifying the keyword ALL
will obtain the sum of all the values in the column:
1 2 3 4 5 6 7 | WITH total_price AS (SELECT SUM(price) tp FROM orders GROUP BY item_id) SELECT SUM(ALL tp) FROM total_price; SUM -------- 119.78 (1 ROW) |
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:
1 2 3 4 5 6 7 | WITH total_price AS (SELECT SUM(price) tp FROM orders GROUP BY item_id) SELECT SUM(DISTINCT tp) FROM total_price; SUM ------- 71.64 (1 ROW) |
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started