# Group Aggregate SQL Functions

> All Group Aggregate functions take an input and group_by sequence argument and produce a result sequence containing the calculated aggregate for each group. The object's sequence is split into groups based on the values in the group_by sequence which is expected to be ordered.

> seq_group_agg_count( input, group_by ) | Returns a sequence with the count of the number of elements in each group
> seq_group_agg_max( input, group_by ) | Returns a sequence with the maximum value for each group of elements
> seq_group_agg_min( input, group_by ) | Returns a sequence with the minimum value for each group of elements
> seq_group_agg_first( input group_by ) | Returns a sequence with the first element of each group
> seq_group_agg_last( input, group_by ) | Returns a sequence with the last element of each group
> seq_group_agg_sum( input, group_by ) | Returns a sequence with the sum of each group of elements
> seq_group_agg_avg( input, group_by ) | Returns a sequence with the average of each group of elements
> seq_group_agg_var( input, group_by ) | Returns a sequence with the variance of each group of elements
> seq_group_agg_var_samp( input, group_by ) | Returns a sequence with the sample variance of each group of elements
> seq_group_agg_dev( input, group_by ) | Returns a sequence with the Standard Deviation of each group of elements
> seq_group_agg_dev_samp( input, group_by ) | Returns a sequence with the Sample Standard Deviation of each group of elements
> seq_group_agg_approxdc( group_by ) | Returns a sequence with the approximate count of distinct values for each group

> ## Example

> As explained in Analytics Functions Library Examples page, the data in the following example is taken from historical values from 2013 for IBM. The data is loaded into xSQL by running various scripts in directory `samples/xsql/scripts/financial`. To reproduce the example below run the `g.bat` (or g`.sh` on Linux systems) specifying the example number 6. For instance, with `scripts/financial` as the current working directory, simply type:

>         g 6

> Following is an example code snippet demonstrating some of the `group_agg()`functions for January 2013 (note that the `group_by` argument is the sequence of trade dates divided by 7 to give grouping by 7-day intervals):

>         -- Show volumes for January 2013
>         SELECT symbol, seq_search(day, 20130101, 20130131) as Jan_13,
>             volume@Jan_13 as "Volume_Jan_13"
>         FROM Quote WHERE symbol = 'SYM0';

>         symbol
>         Jan_13{}
>         Volume_Jan_13{}
>         -------------------------------------------------------------------------
>         SYM0
>         {20130101, 20130104, 20130106, 20130110, 20130123, 20130125, 20130129}
>         {335, 808, 45, 402, 732, 48, 805}

>         -- _max, _min

>         SELECT symbol,
>             seq_search(day, 20130101, 20130131) as Jan_13,
>             seq_group_agg_max(volume@Jan_13, Jan_13/7) as "week_max",
>             seq_group_agg_min(volume@Jan_13, Jan_13/7) as "week_min"
>         FROM Quote WHERE symbol = 'SYM0';

>         symbol
>         Jan_13{}
>          week_max{}
>                week_min{}
>         -------------------------------------------------------------------------
>         SYM0
>         {20130101, 20130104, 20130106, 20130110, 20130123, 20130125, 20130129}
>         {335, 808, 402, 732, 805}
>         {335, 45, 402, 732, 48}

>         -- _first, _last

>         SELECT symbol,
>             seq_search(day, 20130101, 20130131) as Jan_13,
>             seq_group_agg_first(volume@Jan_13, Jan_13/7) as "week_first",
>             seq_group_agg_last(volume@Jan_13, Jan_13/7) as "week_last"
>         FROM Quote WHERE symbol = 'SYM0';

>         symbol
>         Jan_13{}
>          week_first{}
>                week_last{}
>         -------------------------------------------------------------------------
>         SYM0
>         {20130101, 20130104, 20130106, 20130110, 20130123, 20130125, 20130129}
>         {335, 808, 402, 732, 48}
>                {335, 45, 402, 732, 805}

>         -- _sum, _avg

>         SELECT symbol,
>             seq_search(day, 20130101, 20130131) as Jan_13,
>             seq_group_agg_sum(volume@Jan_13, Jan_13/7) as "week_sum",
>             seq_group_agg_avg(volume@Jan_13, Jan_13/7) as "week_avg"
>         FROM Quote WHERE symbol = 'SYM0';

>         symbol
>         Jan_13{}
>          week_sum{}
>                week_avg{}
>         -------------------------------------------------------------------------
>         SYM0
>            {20130101, 20130104, 20130106, 20130110, 20130123, 20130125, 20130129}
>          {335, 853, 402, 732, 853}
>               {335, 426.5, 402, 732, 426.5}

>         -- _var, _dev

>         SELECT symbol,
>             seq_search(day, 20130101, 20130131) as Jan_13,
>             seq_group_agg_var(volume@Jan_13, Jan_13/7) as "week_var",
>             seq_group_agg_dev(volume@Jan_13, Jan_13/7) as "week_dev"
>         FROM Quote WHERE symbol = 'SYM0';

>         symbol
>          Jan_13{}
>          week_var{}
>         week_dev{}
>         -------------------------------------------------------------------------
>         SYM0
>            {20130101, 20130104, 20130106, 20130110, 20130123, 20130125, 20130129}
>         {0, 145542.25, 0, 0, 143262.25}
>         {0, 381.5, 0, 0, 378.5}

>         -- _count, _approxdc

>         SELECT symbol,
>             seq_search(day, 20130101, 20130131) as Jan_13,
>             seq_group_agg_count(Jan_13/7) as "week_count",
>             seq_group_agg_approxdc(volume@Jan_13, Jan_13/7) as "week_approxdc"
>         FROM Quote WHERE symbol = 'SYM0';

>         symbol
>         Jan_13{}
>          week_count{}
>              week_approxdc{}
>         -------------------------------------------------------------------------
>         SYM0
>            {20130101, 20130104, 20130106, 20130110, 20130123, 20130125, 20130129}
>         {1, 2, 1, 1, 2}
>         {1, 2, 1, 1, 2}