SUM Function

Description

The SUM function returns the sum of expressions of all rows. Only one expression is specified as a parameter. You can get the sum without duplicates by inserting the DISTINCT or UNIQUE keyword in front of the expression, or get the sum of all values by omitting the keyword or by using ALL.

For analytic function examples, see the following analytic function examples. For more information, see Overview.

Syntax

SUM ( [ { DISTINCT | DISTINCTROW } | UNIQUE | ALL ] expression )

• expression: Specifies an expression that returns a numeric value.
• ALL: Gets the sum for all data (default).
• DISTINCT or UNIQUE: Gets the sum of unique values without duplicates

Example of Aggregate Function

The following is an example that outputs the top 10 countries and the total number of gold medals based on the sum of gold medals won in the Olympic Games in demodb.

SELECT nation_code, SUM(gold) FROM participant GROUP BY nation_code

ORDER BY SUM(gold) DESC

FOR ORDERBY_NUM() BETWEEN 1 AND 10 ;

=== <Result of SELECT Command in Line 1> ===

  nation_code             sum(gold)

===================================

  'USA'                         190

  'CHN'                          97

  'RUS'                          85

  'GER'                          79

  'URS'                          55

  'FRA'                          53

  'AUS'                          52

  'ITA'                          48

  'KOR'                          48

  'EUN'                          45

Example of Analytic Function

The following example shows how to output the number of gold medals by year and the average sum of the accumulated gold medals to the year acquired by the country whose nation_code code starts with 'AU' in demodb.

SELECT host_year, nation_code, gold,

SUM(gold) OVER (PARTITION BY nation_code ORDER BY host_year) sum_gold

FROM participant WHERE nation_code LIKE 'AU%';

    host_year  nation_code                  gold     sum_gold

=============================================================

         1988  'AUS'                           3            3

         1992  'AUS'                           7           10

         1996  'AUS'                           9           19

         2000  'AUS'                          16           35

         2004  'AUS'                          17           52

         1988  'AUT'                           1            1

         1992  'AUT'                           0            1

         1996  'AUT'                           0            1

         2000  'AUT'                           2            3

         2004  'AUT'                           2            5

The following example is removing the "ORDER BY host_year" clause under the OVER analysis clause from the above example. The avg_gold value is the average of gold medals for all years,+ so the value is identical for every year by nation_code.

SELECT host_year, nation_code, gold, SUM(gold) OVER (PARTITION BY nation_code) sum_gold

FROM participant WHERE nation_code LIKE 'AU%';

    host_year  nation_code                  gold     sum_gold

=============================================================

         2004  'AUS'                          17           52

         2000  'AUS'                          16           52

         1996  'AUS'                           9           52

         1992  'AUS'                           7           52

         1988  'AUS'                           3           52

         2004  'AUT'                           2            5

         2000  'AUT'                           2            5

         1996  'AUT'                           0            5

         1992  'AUT'                           0            5

         1988  'AUT'                           1            5