<< Click to Display Table of Contents >> ## SUM |

SUM ( [DISTINCT | ALL] <number-expression> ) [ OVER <window-specification> ]

SUM returns the sum of all the value expressions evaluated for each row in the aggregation. When used without the OVER clause, SUM is considered an aggregate function. When used with the OVER clause, it is an analytic function.

If DISTINCT is specified, only distinct row values qualify. If ALL is specified, all rows qualify. If neither DISTINCT nor ALL is specified, the behavior defaults to ALL.

When used as an analytic function, SUM will return null if the window being evaluated (or in the case of a PARTITION BY, the partition within the window matching the input row) contains no rows.

Examples

The following example shows the difference between SUM(ALL value) and SUM(DISTINCT value); note how SUM(ALL value) returns 710 (71 rows with a value of 10 plus 1 row with a value of 0) whereas SUM(DISTINCT value) returns just 10 (10 + 0) as there are only two distinct values for PCT_FREE.

0: jdbc:farrago:rmi://localhost> select pct_free, count(*) from test1 group by pct_free;

+-----------+---------+

| PCT_FREE | EXPR$1 |

+-----------+---------+

| 10.0 | 71 |

| 0.0 | 1 |

+-----------+---------+

2 rows selected (0.453 seconds)

0: jdbc:farrago:rmi://localhost> select sum(all pct_free) as sum_all from test1;

+----------+

| SUM_ALL |

+----------+

| 710.0 |

+----------+

1 row selected (0.391 seconds)

0: jdbc:farrago:rmi://localhost> select sum(distinct pct_free) as sum_distinct from test1;

+---------------+

| SUM_DISTINCT |

+---------------+

| 10.0 |

+---------------+

1 row selected (0.422 seconds)

SUM is only supported on numeric types.

Limitation

SQLstream streaming SQL does not support SUM applied to interval types. This is a departure from the SQL:2008 standard.