DB2 Tutorial

  • Home
  • Start Here
  • Basics
  • Views
  • Triggers
  • Indexes
  • Functions
    • Aggregate Functions
    • Date Functions
    • String Functions
    • Window Functions
Home » Db2 Aggregate Functions » Db2 SUM

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;
db2 sum function with having clause example

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

  • Was this tutorial helpful ?
  • YesNo
Previous Db2 MIN

Getting Started

  • What is Db2
  • Installing Db2 Database Server
  • Db2 Sample Database
  • Creating a Db2 Sample Database
  • Connecting to a Db2 Database
  • Interacting with Db2 using SQL Developer

Data Manipulation

  • SELECT
  • ORDER BY
  • WHERE
  • SELECT DISTINCT
  • AND
  • OR
  • BETWEEN
  • LIKE
  • IN
  • LIMIT
  • FETCH
  • Join
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • Self-Join
  • CROSS JOIN
  • GROUP BY
  • Subquery
  • HAVING
  • UNION
  • INTERSECT
  • EXCEPT
  • Common Table Expression or CTE
  • INSERT
  • INSERT Multiple Rows
  • INSERT INTO SELECT
  • UPDATE
  • DELETE

Managing Database Objects

  • CREATE TABLE
  • Identity Columns
  • ALTER COLUMN
  • ADD COLUMN
  • DROP COLUMN
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME TABLE

Db2 Constraints

  • Primary Key
  • Foreign Key
  • DEFAULT
  • NOT NULL
  • CHECK
  • UNIQUE

Db2 Data Types

  • Integer
  • Decimal
  • VARCHAR
  • CHAR
  • DATE
  • TIME
  • TIMESTAMP

Useful Functions & Expressions

  • CAST
  • CASE Expression
  • COALESCE

About db2tutorial.com

The db2tutorial.com website provides you with a comprehensive IBM DB2 tutorial with many practical examples and hands-on sessions.

Recent Tutorials

  • Db2 Functions
  • Db2 RANK
  • Db2 ROW_NUMBER
  • Db2 Window Functions
  • What is Db2

Site Links

  • Home
  • About Us
  • Contact Us
  • Privacy Policy
Copyright © © 2021 by www.db2tutorial.com. All Rights Reserved.