# Analytics Functions Example 11: Hash Aggregate

> ## Hash Aggregate Average

> Compute the average `close` value for groups of trades having `volume` values grouped by millions of trades per day (for the first two weeks of March 2013):

>         SELECT symbol,
>             seq_search(day, 20130304, 20130315) as Mar_2013,
>             volume@Mar_2013 as "volume", close@Mar_2013 as "close",
>             seq_hash_agg_avg(close@Mar_2013, volume@Mar_2013/1000000) as avg_vol,
>             seq_hash_group_by(avg_vol) as "group_by"
>         FROM Quote WHERE symbol='IBM';

>         symbol
>         Mar_2013{}
>         volume{}
>         close{}
>         avg_vol{}
>         group_by{}
>         ------------------------------------------------------------------------------
>         IBM
>         {20130304, 20130305, 20130306, 20130307, 20130308,
>         20130311, 20130312, 20130313, 20130314, 20130315}
>         {3693365, 3807706, 3594899, 3884317, 3700786,
>         3049701, 3592096, 3356946, 5505584, 7936544}
>         {205.190002, 206.529999, 208.380005, 209.419998, 210.380005,
>         210.080002, 210.550003, 212.059998, 215.800003, 214.919998}
>         {214.919998, 209.073751, 215.800003}
>         {7, 3, 5}

> Note that the "hash_aggregate" functions calculate aggregate values for each group determined by an arbitrary sequence (not necessarily sorted, such as `close` above) using a hash table. So to interpret the result sets from these functions a “group_by” sequence is required. The function `seq_hash_group_by` returns the sequence of values used for grouping in order to make the correspondence between the values in the result sequence(s) and the groups.

> In the example above, the `close` prices are divided into groups associated with `volume` values where the `volume` is in ranges of `(0-999,999)`, `(1,000,000-1,999,999)`, `(2,000,000-2,999,999)`, etc. To illustrate we analyze the output as follows:

> - We have highlighted values in the output corresponding to the second element of the `group_by` sequence with value 3 (the last sequence in the output).
> - The corresponding value in the result sequence `avg_vol` is (209.073751).
> - To see how this is computed, we have highlighted the elements in the `volume` sequence having trading `volume` between 3M and 4M (`volume/1000000 = 3`). These values have position indexes of `(0,1,2,3,4,5,6)`.
> - The `close` values for elements 0 through 6 are averaged as follows:

> `(205.190002 + 206.529999 + 208.380005 + 209.419998 + 210.380005 + 210.080002 + 210.550003 + 212.059998)``/ 8 =` `209.073751`

> Note also that the single occurrence of `volume` in the 5M range corresponds to the single `avg_vol` value `215.800003`, and the single occurrence of `volume` in the 7M range corresponds to the single `avg_vol` value `214.919998`.

> ## To run this example

> A sample script to demonstrate this `select` statement using xSQL can be run from the `samples/xsql/scripts/financial` directory with the following command:

>         x 11