`g_sum(G;S;X)`

Returns the sum of values within a given group.

Function type

Vector only

Syntax

g_sum(G;S;X)

Input

Argument Type Description

Argument	Type	Description
`G`	any	A space- or comma-separated list of column names Rows are in the same group if their values for all of the columns listed in `G` are the same. If `G` is omitted, all rows are considered to be in the same group. If any of the columns listed in `G` contain N/A, the N/A value is considered a valid grouping value.
`S`	integer	The name of a column in which every row evaluates to a 1 or 0, which determines whether or not that row is selected to be included in the calculation If `S` is omitted, all rows will be considered by the function (subject to any prior row selections). If any of the values in `S` are neither 1 nor 0, an error is returned.
`X`	any numeric type	A column name N/As in `X` are ignored (i.e., the result is the sum of the non-N/A values).

G

any

A space- or comma-separated list of column names

Rows are in the same group if their values for all of the columns listed in G are the same.

If G is omitted, all rows are considered to be in the same group.

If any of the columns listed in G contain N/A, the N/A value is considered a valid grouping value.

S

integer

The name of a column in which every row evaluates to a 1 or 0, which determines whether or not that row is selected to be included in the calculation

If S is omitted, all rows will be considered by the function (subject to any prior row selections).

If any of the values in S are neither 1 nor 0, an error is returned.

X

any numeric type

A column name

N/As in X are ignored (i.e., the result is the sum of the non-N/A values).

Return Value

For every row in each group defined by G (and for those rows where S=1, if specified), g_sum returns a decimal value corresponding to the sum of the values in the column listed in X that are in the same group as that row.

If all values for a particular group are N/A, the result is 0.

Sample Usage

<base table="pub.doc.samples.ref.func.g_func_sample_usage"/>
<willbe name="g_sum_1" value="g_sum(state;include;value)"/>
<willbe name="g_sum_2" value="g_sum(state city;include;value)"/>

Example

Let's say we want to figure out the total sales for every store in our Sales Item Detail table (pub.demo.retail.item).

<base table="pub.demo.retail.item"/>

Create a computed column using the g_sum(G;S;X) function. Specify store as the group (G), since we're grouping by store; specify sales as X since we want the sum of sales; and leave the selection parameter (S) blank, since we want to take into account all of the transactions (not just a subset of those transactions):

<willbe name="salesbystore" value="g_sum(store;;sales)" label="Sales By`Store"/>

Since we didn't specify a selection column, the salesbystore column will show the total sales for every store.

Now let's find the total amount of sales for each transaction in every store. To do this, we group by both store and transid:

<willbe name="salesbystoreandtransid" value="g_sum(store transid;;sales)" label="Sales By`Transaction`(By Store)"/>

Now let's see if we can find out that same information but only for those transactions that occurred in May. For this, we can use the selection column parameter of the g_sum(G;S;X) function.

Create a computed column that evaluates to true if the transaction date occurs in May:

<willbe name="may" value="between(date;'5/1/2012';'5/31/2012')" label="May"/>

Now, use the may computed column as the selection column in the g_sum(G;S;X) function:

<willbe name="salesbystoreandtransid_may" value="g_sum(store transid;may;sales)" label="May Sales By`Transaction`(By Store)"/>

The salesbystoreandtransid_may column now shows the total sales for each transaction that occurred between 5/1/2012 and 5/31/2012 in every store.