Db2 SUM

Summary: in this tutorial, you will learn how to use the DB2 SUM() function to calculate the sum of values.

Introduction to Db2 SUM() function

The Db2 SUM() function is an aggregate function that returns the sum of a set of values.

The following is the syntax of the SUM() function:

SUM(ALL | DISTINCT expression)

The SUM() function accepts a set of numeric values and returns the sum of them.

If you use the DISTINCT option, the SUM() function will only return the sum of distinct values. If you use the ALL option, the SUM() function considers all values including duplicates in the calculation. The default is ALL.

The SUM() function determines the data type of the result based on these rules:

• If the argument is SMALLINT or INT, the return type is BIGINT.
• If the argument is single precision floating-point REAL, the return type is double precision floating-point DOUBLE.
• If the argument is DECFLOAT(n), the return type is DECFLOAT(34).
• Otherwise, the return type is the same as the type of the argument.

The SUM() function returns NULL if the set is empty or all values are NULL.

Note that the SUM() function ignores NULL in the calculation.

Db2 SUM() illustration

First, create a new table named sum_demo that contains one integer column:

CREATE TABLE sum_demo(c1 INT);

Second, insert five rows into the sum_demo table:

INSERT INTO sum_demo(c1) 
VALUES(1),(2),(3),(3),(NULL);

Third, view the data from the sum_demo table by using this SELECT statement:

SELECT c1 FROM sum_demo;

Fourth, use the SUM() function to return the sum of values in the c1 column of the sum_demo table, including duplicates.

SELECT 
    SUM(c1) 
FROM 
    sum_demo;

Here is the output:

TOTAL       
----------- 
9      

In this example, the SUM() function includes the duplicates because it uses ALL option by default.

Fifth, use the DISTINCT option in the SUM() function to calculate the sum of distinct values.

SELECT 
    SUM(DISTINCT c1) total_distinct
FROM 
    sum_demo;

The output is the following:

TOTAL_DISTINCT 
-------------- 
6      

In this example, the SUM() function ignores one duplicate value (3) in its calculation because of the DISTINCT option.

Db2 SUM() examples

We’ll use the books table from the sample database to demonstrate the SUM() function.

1) Using DB2 SUM() function to calculate the sum of values example

This example uses the SUM() function to calculate the total of pages of all books in the books table:

SELECT 
    SUM(total_pages) pages
FROM
    books;

Here is the output:

PAGES       
----------- 
508504  

2) Using DB2 SUM() function with the GROUP BY clause example

The SUM() function is usually used with the GROUP BY clause to return the totals of groups.

This example returns the total pages of books by publishers:

SELECT 
    p.name publisher, 
    SUM(b.total_pages) total_pages
FROM 
    books b
    INNER JOIN publishers p
        ON p.publisher_id = b.publisher_id
WHERE 
    total_pages IS NOT NULL
GROUP BY 
    p.name
ORDER BY total_pages DESC;	

This picture displays the partial output:

In this example, the GROUP BY clause groups books by publishers and the SUM() function returns the total number of pages for every publisher.

3) Using DB2 SUM() function with the HAVING clause example

The following statement finds the publishers whose number of pages are higher than 20,000:

SELECT 
    p.name publisher, 
    SUM(b.total_pages) total_pages
FROM 
    books b
    INNER JOIN publishers p
        ON p.publisher_id = b.publisher_id
WHERE 
    total_pages IS NOT NULL
GROUP BY 
    p.name
HAVING
    SUM(b.total_pages) > 20000
ORDER BY total_pages DESC;	

In this tutorial, you have learned how to use the Db2 SUM() function to calculate the total of a set of values.