# Hash Aggregate Grouping

> ## Hash Aggregate Grouping C Functions

> The Hash Aggregate C API functions group aggregates based on hash functions. So, unlike the `mco_seq_group_agg_TYPE()` APIs, these functions do not require that the grouping sequence be ordered. Also unlike all other sequence functions, this group of functions allocates dynamic memory for the hash table. If the n_groups parameter is 0, then the internal database runtime parameter `mco_seq_hash_init_size` is used to specify the size of the hash table and it is automatically extended when the threshold determined by `mco_seq_hash_load_factor` is reached. This dynamic hash table memory should be explicitly released by calling `mco_seq_free_hash()`.

> The result of all Hash Aggregate functions is returned in the result_agg or result_count sequence as the calculated aggregate or count for each group. The input sequence is split into groups based on the group_by sequence. The argument result_group_by returns the sequence of values used for grouping in order for the application to make the correspondence between the values in the result sequence and the groups.

> For example, consider the following code snippet demonstrating how to use a hash aggregate function to calculate the average Close price for Volume values in ranges of 0..9, 10..19, 20..29, etc.:

>         {
>             mco_trans_h trans;
>             mco_cursor_t quote_cursor;
>             Quote quote;
>             mco_seq_iterator_t close_iterator, volume_iterator, volume_div_10_iterator;
>             mco_seq_iterator_t group_iterator,  avg_iterator;
>             MCO_RET rc;
>             ...

>             for (rc = mco_cursor_first(trans, &quote_cursor);
>                 rc != MCO_S_CURSOR_END;
>                 rc = mco_cursor_next(trans, &quote_cursor))
>             {
>                 Quote_from_cursor(trans, &quote_cursor, &quote);
>                 Quote_close_iterator(&quote, &close_iterator);
>                 Quote_volume_iterator(&quote, &volume_iterator);

>                 rc = mco_seq_const_uint4(&const_iterator, 10);
>                 rc = mco_seq_div_uint4(&volume_div_10_iterator, &volume_iterator, &const_iterator);
>                 rc = mco_seq_hash_agg_avg_float(&avg_iterator, &group_iterator,

>                         &close_iterator, &volume_div_10_iterator, 0);

>                 ...
>                 mco_seq_free_hash(&avg_iterator);
>             }
>             ...
>         }

> Suppose that we have Volume and Close sequences with the following thirteen values:

>         Volume: {335, 808, 45, 402, 732, 48, 805, 450, 385, 420, 367, 69, 77}

>         Close: {62.830, 45.790, 74.730, 53.460, 67.870, 50.370, 80.320,
>                 29.940, 25.920, 37.280, 80.920, 57.730, 43.850}

> To split the Volume sequence into groups associated with ranges of 0..9, 10..19, 20..29, etc., we created the constant sequence iterator with values of 10 and use it as input to the `mco_seq_div_uint4()` API. This produces the following sequence values which are returned in the result_group_by sequence (`group_iterator` in this example) :

>         {80, 73, 7, 38, 4, 40, 6, 45, 33, 42, 36}

> Note that these values do not correspond to the positions of the input sequence. In fact the highlighted value 80 is the result of dividing the second and seventh elements of the Volume sequence. So the Volume values that fall into the 800-810 range (i.e. `808` and `805`) are the first group.

> For this input, the resulting average Close sequence returned in the result_agg sequence (`avg_iterator` in this example) by function `mco_seq_hash_agg_avg_float()` will have the following values:

>         {63.055, 67.870, 43.850, 25.920, 62.550, 53.460, 57.730, 29.940, 62.830, 37.280, 80.920}

> To associate the values in the resulting averages with the corresponding group of average Close values and verify the computation, note the following:

> - the first element of the result_group_by sequence (`80`) corresponds with Volumes in the range 800-810 (where Volume `/ 10 = 80`);

> - the values for Volume in the range 800-810 are `808` in element in position 1 (zero-based index) and `805` in position 6;

> - the Close values in the corresponding elements 1 and 6 are `45.790` and `80.320`;
> - the average is `(45.790 + 80.320) / 2 = 63.055` which is the value in position 1 of the result sequence.

> ## Hash Aggregate Grouping SQL Functions

> The SQL API provides function `seq_hash_group_by()` to produce the grouping values iterator used as input to the `seq_hash_agg_*()` functions.