SUM [analytic]

Computes the sum of an expression over a group of rows within a.

Computes the sum of an expression over a group of rows within a window. It returns a DOUBLE PRECISION value for a floating-point expression. Otherwise, the return value is the same as the expression data type.

Behavior type

Immutable

Syntax

SUM ( expression ) OVER (
    [ window-partition-clause ]
    [ window-order-clause ]
    [ window-frame-clause ] )

Parameters

expression
Any NUMERIC data type or any non-numeric data type that can be implicitly converted to a numeric data type. The function returns the same data type as the numeric data type of the argument.
OVER()
See Analytic Functions

Overflow handling

If you encounter data overflow when using SUM, use SUM_FLOAT which converts data to a floating point.

By default, Vertica allows silent numeric overflow when you call this function on numeric data types. For more information on this behavior and how to change it, seeNumeric data type overflow with SUM, SUM_FLOAT, and AVG.

Examples

The following query returns the cumulative sum all of the returns made to stores in January:

=> SELECT calendar_month_name AS month, transaction_type, sales_quantity,
     SUM(sales_quantity)
     OVER (PARTITION BY calendar_month_name ORDER BY date_dimension.date_key) AS SUM
     FROM store.store_sales_fact JOIN date_dimension
     USING(date_key) WHERE calendar_month_name IN ('January')
     AND transaction_type= 'return';
  month  | transaction_type | sales_quantity | SUM
---------+------------------+----------------+------
 January | return           |              7 |  651
 January | return           |              3 |  651
 January | return           |              7 |  651
 January | return           |              7 |  651
 January | return           |              7 |  651
 January | return           |              3 |  651
 January | return           |              7 |  651
 January | return           |              5 |  651
 January | return           |              1 |  651
 January | return           |              6 |  651
 January | return           |              6 |  651
 January | return           |              3 |  651
 January | return           |              9 |  651
 January | return           |              7 |  651
 January | return           |              6 |  651
 January | return           |              8 |  651
 January | return           |              7 |  651
 January | return           |              2 |  651
 January | return           |              4 |  651
 January | return           |              5 |  651
 January | return           |              7 |  651
 January | return           |              8 |  651
 January | return           |              4 |  651
 January | return           |             10 |  651
 January | return           |              6 |  651
 ...

See also