PostgreSQL SUM for ObjectRocket Instance

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

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 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':

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

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.