Computes the sum of all values found in all row values in a column. Input column can be of Integer or Decimal.
- If a row contains a missing or null value, it is not factored into the calculation. If no numeric values are found in the source column, the function returns
0. - When used in a
pivottransform, the function is computed for each instance of the value specified in thegroupparameter. See Pivot Transform.
For a version of this function computed over a rolling window of rows, see ROLLINGSUM Function.
Basic Usage
pivot value:SUM(myRating) group:postal_code limit:1
Output: Generates a two-column column containing the unique values of the postal_code column and the sum of the group of values from the myRating column for the postal_code value. The limit parameter defines the maximum number of output columns.
Syntax and Arguments
pivot value:SUM(function_col_ref) [group:group_col_ref] [limit:limit_count]
| Argument | Required? | Data Type | Description |
|---|---|---|---|
| function_col_ref | Y | string | Name of column to which to apply the function |
For more information on the group and limit parameters, see Pivot Transform.
For more information on syntax standards, see Language Documentation Syntax Notes.
function_col_ref
Name of the column the values of which you want to calculate the sum. Column must contain Integer or Decimal values.
- Literal values are not supported as inputs.
- Multiple columns and wildcards are not supported.
Usage Notes:
| Required? | Data Type | Example Value |
|---|---|---|
| Yes | String (column reference) | myValues |
Tip: For additional examples, see Common Tasks.
Examples
Source: Transform: To track by month, you need a column containing the month value extracted from the date:
set col:Date value:DATEFORMAT(Date, 'MMM yyyy') pivot value: LIST(Color, 1000) SUM(Qty) group: Date limit:1 If needed, you can unpack the list array data using the following:
unnest col:list_ColorOrderId Date Item Qty Color 1001 1/15/15 Pants 1 red 1002 1/15/15 Shirt 2 green 1003 1/15/15 Hat 3 blue 1004 1/16/15 Shirt 4 yellow 1005 1/16/15 Hat 5 red 1006 1/20/15 Pants 6 green 1007 1/15/15 Hat 7 blue 1008 4/15/15 Shirt 8 yellow 1009 4/15/15 Shoes 9 brown 1010 4/16/15 Pants 1 red 1011 4/16/15 Hat 2 green 1012 4/16/15 Shirt 3 blue 1013 4/20/15 Shoes 4 black 1014 4/20/15 Hat 5 blue 1015 4/20/15 Pants 6 black Date values, and apply the LIST function to the Color column. In the same aggregation, you can include a summation function for the Qty column:
Date list_Color sum_Qty Jan 2015 ["green","blue","blue","red","green","red","yellow"] 28 Apr 2015 38
This page has no comments.