SQL Sum() Function

This post explores on how to use the sum function in Standard SQL. The sum() function allows us to perform an aggregate operation on a set of values. For example, we can use the sum() function to calculate the total or sum of a given set of values.

The input set can be an array, a column, or values which are returned from another SQL expression. The sum function is one of the fundamental SQL aggregation functions.

SQL Sum Function Syntax

The following code snippet shows the syntax for the sum function in SQL:

SUM([DISTINCT] column_or_expression);

The function accepts the column or expression with the values whose sum you wish to calculate. We can also use the DISTINCT keyword to skip the duplicate or repeating values.

The function then determines the sum of the provided values and returns a single value.

Let us discuss some basic uses of the sum function.

Sample Data

Before proceeding, let us look at the layout of the sample data that we wish to query. First, you can check the table schema and insert the statements which are provided in the following:

CREATE DATABASE products;
CREATE TABLE hello.store (
id INTEGER NOT NULL,
product_name STRING NOT NULL,
quantity INTEGER,
price INTEGER NOT NULL,
);

Once created, we can insert the following sample data:

INSERT INTO
hello.store (id,
product_name,
quantity,
price)
VALUES
(1, "Product 1", 10, 100),
(2, "Product 2", 5, 50),
(3, "Product 3", 20, 200),
(4, "Product 4", 15, 150),
(5, "Product 5", 25, 250),
(6, "Product 6", 30, 300),
(7, "Product 7", 35, 350),
(8, "Product 8", 40, 400),
(9, "Product 9", 45, 450),
(10, "Product 10", 50, 500);

Let us now see some examples on how we can use the sum function in SQL.

Example 1: Using the Sum Function with a Single Column

We can find the sum of values in a given column using the sum and the column name as shown in the following example:

SELECT SUM(price) AS total_price FROM hello.store;

This should return the total price for all products as follows:

total_price
2750

Example 2: Using the Sun Function with Multiple Columns

We can also create an expression based on multiple table columns in conjunction with the sum() function as shown in the following:

SELECT
SUM(quantity*price) AS total_stock
FROM
hello.store;

This should return as follows:

total_stock
96250

Conclusion

In this tutorial, you learned how to use the SQL SUM function to calculate the sum of the values in a specified column or expression. We often use the sum function in combination with the SELECT statement to calculate the total of a set of values.