Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

SUM Function

The SUM function returns the sum of the input column or expression values. The SUM function works with numeric values and ignores NULL values.

Syntax

Copy
SUM ( [ DISTINCT | ALL ] expression )

Arguments

expression

The target column or expression that the function operates on.

DISTINCT | ALL

With the argument DISTINCT, the function eliminates all duplicate values from the specified expression before calculating the sum. With the argument ALL, the function retains all duplicate values from the expression for calculating the sum. ALL is the default.

Data Types

The argument types supported by the SUM function are SMALLINT, INTEGER, BIGINT, NUMERIC, DECIMAL, REAL, and DOUBLE PRECISION.

The return types supported by the SUM function are

  • BIGINT for BIGINT, SMALLINT, and INTEGER arguments

  • NUMERIC for NUMERIC arguments

  • DOUBLE PRECISION for floating point arguments

The default precision for a SUM function result with a 64-bit NUMERIC or DECIMAL argument is 19. The default precision for a result with a 128-bit NUMERIC or DECIMAL argument is 38. The scale of the result is the same as the scale of the argument. For example, a SUM of a DEC(5,2) column returns a DEC(19,2) data type.

Examples

Find the sum of all commissions paid from the SALES table:

Copy
select sum(commission) from sales; sum ------------- 16614814.65 (1 row)

Find the number of seats in all venues in the state of Florida:

Copy
select sum(venueseats) from venue where venuestate = 'FL'; sum -------- 250411 (1 row)

Find the number of seats sold in May:

Copy
select sum(qtysold) from sales, date where sales.dateid = date.dateid and date.month = 'MAY'; sum ------- 32291 (1 row)