`g_cnt(G;S)`

Returns the number of rows within a given group.

Function type

Vector only

Syntax

g_cnt(G;S)

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.

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.

Return Value

For every row in each group defined by G (and for those rows where S=1, if specified), g_cnt returns the integer value corresponding to the number of rows that are in the same group as that row.

Sample Usage

<base table="pub.doc.samples.ref.func.g_func_sample_usage"/>
<willbe name="g_cnt_1" value="g_cnt(state;include)"/>
<willbe name="g_cnt_2" value="g_cnt(state city;include)"/>

Example

Let's say we want to know the number of transactions that occurred at each store in our Sales Item Detail table (pub.demo.retail.item).

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

Create a computed column using the g_cnt(G;S) function. Specify store as the group (G), since we're grouping by store, 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="numtrans" value="g_cnt(store;)" label="# Transactions"/>

Since we didn't specify a selection column, the numtrans column will show the total number of transactions for every store.

Now let's see if we can find out how many transactions occurred at each store between 6/1/2012 and 6/30/2012. We essentially want to find out the same information, but for a subset of our data. For this, we can use the selection column parameter of the g_cnt(G;S) function.

Create a computed column that evaluates to true if the transaction date falls between those dates:

<willbe name="in_june" value="between(date;'6/1/2012';'6/30/2012')" label="In June"/>

Now, use the in_june computed column as the selection column in the g_cnt(G;S) function:

<willbe name="numtrans_in_june" value="g_cnt(store;in_june)" label="# Transactions`In June"/>

The numtrans_in_june column shows the total number of transactions that occurred between 6/1/2012 and 6/30/2012 for every store.