# cgroup by

The cgroup by (cumulative group) clause performs cumulative grouping calculations. Calculations are conducted in the first cgroup by group, the first 2 cgroup by groups, the first 3 cgroup by groups, and so on.

A SQL statement with cgroup by clause must also use order by clause to sort the grouping results before conducting the cumulative calculation.

If the SQL statement uses the cgroup by clause, the execution sequence is as follows: first apply the filtering conditions (if any), and then conduct the calculations in the select clause within the groups determined by the cgroup by and group by (if any) columns, then sort the grouping calculation results according to the required order by columns (must belong to the cgroup by and group by (if any) columns), and finally calculate the cumulative value of the grouping calculation results. If group by is used, the cumulative value is calculated within each group by group.

A SQL statement with cgroup by clause only supports the following aggregate functions: sum, sum2, sum3, sum4, prod, max, min, first, last, count, size, avg, std, var, skew, kurtosis, wsum, wavg, corr, covar, contextCount, contextSum, contextSum2.

Examples

The following example uses the cgroup by clause to calculate volume-weighted average price (vwap).

```t = table(`A`A`A`A`B`B`B`B as sym, 09:30:06 09:30:28 09:31:46 09:31:59 09:30:19 09:30:43 09:31:23 09:31:56 as time, 10 20 10 30 20 40 30 30 as volume, 10.05 10.06 10.07 10.05 20.12 20.13 20.14 20.15 as price);
t;
```

sym

time

volume

price

A

09:30:06

10

10.05

A

09:30:28

20

10.06

A

09:31:46

10

10.07

A

09:31:59

30

10.05

B

09:30:19

20

20.12

B

09:30:43

40

20.13

B

09:31:23

30

20.14

B

09:31:56

30

20.15

```select wavg(price, volume) as wvap from t where sym=`A cgroup by minute(time) as minute order by minute;
```

time

wvap

09:30m

10.056667

09:31m

10.055714

cgroup by can be used together with group by:

```select wavg(price, volume) as wvap from t group by sym cgroup by minute(time) as minute order by minute;
```

sym

minute

wvap

A

09:30m

10.056667

B

09:30m

20.126667

A

09:31m

10.055714

B

09:31m

20.135833

```select wavg(price, volume) as wvap from t group by sym cgroup by minute(time) as minute order by sym, minute;
```

sym

minute

wvap

A

09:30m

10.056667

A

09:31m

10.055714

B

09:30m

20.126667

B

09:31m

20.135833