title_wip

The same query also can be written using `WITH` keyword.

```
SELECT year, month, day, count(*) FROM t GROUP BY year, month, day WITH CUBE;
```

**See also**

- [group\_by\_use\_nulls](/docs/operations/settings/settings#group_by_use_nulls) setting for SQL standard compatibility.

## WITH TOTALS Modifier[​](#with-totals-modifier "Direct link to WITH TOTALS Modifier")

If the `WITH TOTALS` modifier is specified, another row will be calculated. This row will have key columns containing default values (zeros or empty lines), and columns of aggregate functions with the values calculated across all the rows (the "total" values).

This extra row is only produced in `JSON*`, `TabSeparated*`, and `Pretty*` formats, separately from the other rows:

- In `XML` and `JSON*` formats, this row is output as a separate 'totals' field.
- In `TabSeparated*`, `CSV*` and `Vertical` formats, the row comes after the main result, preceded by an empty row (after the other data).
- In `Pretty*` formats, the row is output as a separate table after the main result.
- In `Template` format, the row is output according to specified template.
- In the other formats it is not available.

Note

totals is output in the results of `SELECT` queries, and is not output in `INSERT INTO ... SELECT`.

`WITH TOTALS` can be run in different ways when [HAVING](/docs/sql-reference/statements/select/having) is present. The behavior depends on the `totals_mode` setting.

### Configuring Totals Processing[​](#configuring-totals-processing "Direct link to Configuring Totals Processing")

By default, `totals_mode = 'before_having'`. In this case, 'totals' is calculated across all rows, including the ones that do not pass through HAVING and `max_rows_to_group_by`.

The other alternatives include only the rows that pass through HAVING in 'totals', and behave differently with the setting `max_rows_to_group_by` and `group_by_overflow_mode = 'any'`.

`after_having_exclusive` – Don't include rows that didn't pass through `max_rows_to_group_by`. In other words, 'totals' will have less than or the same number of rows as it would if `max_rows_to_group_by` were omitted.

`after_having_inclusive` – Include all the rows that didn't pass through 'max\_rows\_to\_group\_by' in 'totals'. In other words, 'totals' will have more than or the same number of rows as it would if `max_rows_to_group_by` were omitted.

`after_having_auto` – Count the number of rows that passed through HAVING. If it is more than a certain amount (by default, 50%), include all the rows that didn't pass through 'max\_rows\_to\_group\_by' in 'totals'. Otherwise, do not include them.

`totals_auto_threshold` – By default, 0.5. The coefficient for `after_having_auto`.

If `max_rows_to_group_by` and `group_by_overflow_mode = 'any'` are not used, all variations of `after_having` are the same, and you can use any of them (for example, `after_having_auto`).

You can use `WITH TOTALS` in subqueries, including subqueries in the [JOIN](/docs/sql-reference/statements/select/join) clause (in this case, the respective total values are combined).

## GROUP BY ALL[​](#group-by-all "Direct link to GROUP BY ALL")

`GROUP BY ALL` is equivalent to listing all the SELECT-ed expressions that are not aggregate functions.

For example:

```
SELECT
    a * 2,
    b,
    count(c),
FROM t
GROUP BY ALL
```

is the same as

```
SELECT
    a * 2,
    b,
    count(c),
FROM t
GROUP BY a * 2, b
```

For a special case that if there is a function having both aggregate functions and other fields as its arguments, the `GROUP BY` keys will contain the maximum non-aggregate fields we can extract from it.

For example:

```
SELECT
    substring(a, 4, 2),
    substring(substring(a, 1, 2), 1, count(b))
FROM t
GROUP BY ALL
```

is the same as

```
SELECT
    substring(a, 4, 2),
    substring(substring(a, 1, 2), 1, count(b))
FROM t
GROUP BY substring(a, 4, 2), substring(a, 1, 2)
```

## Examples[​](#examples "Direct link to Examples")

Example:

```
SELECT
    count(),
    median(FetchTiming > 60 ? 60 : FetchTiming),
    count() - sum(Refresh)
FROM hits
```

As opposed to MySQL (and conforming to standard SQL), you can't get some value of some column that is not in a key or aggregate function (except constant expressions). To work around this, you can use the 'any' aggregate function (get the first encountered value) or 'min/max'.

Example:

```
SELECT
    domainWithoutWWW(URL) AS domain,
    count(),
    any(Title) AS title -- getting the first occurred page header for each domain.
FROM hits
GROUP BY domain
```

For every different key value encountered, `GROUP BY` calculates a set of aggregate function values.

## GROUPING SETS modifier[​](#grouping-sets-modifier "Direct link to GROUPING SETS modifier")

This is the most general modifier.
This modifier allows manually specifying several aggregation key sets (grouping sets).
Aggregation is performed separately for each grouping set, and after that, all results are combined.
If a column is not presented in a grouping set, it's filled with a default value.

In other words, modifiers described above can be represented via `GROUPING SETS`.
Despite the fact that queries with `ROLLUP`, `CUBE` and `GROUPING SETS` modifiers are syntactically equal, they may perform differently.
When `GROUPING SETS` try to execute everything in parallel, `ROLLUP` and `CUBE` are executing the final merging of the aggregates in a single thread.

In the situation when source columns contain default values, it might be hard to distinguish if a row is a part of the aggregation which uses those columns as keys or not.
To solve this problem `GROUPING` function must be used.

**Example**

The following two queries are equivalent.

```
-- Query 1
SELECT year, month, day, count(*) FROM t GROUP BY year, month, day WITH ROLLUP;

-- Query 2
SELECT year, month, day, count(*) FROM t GROUP BY
GROUPING SETS
(
    (year, month, day),
    (year, month),
    (year),
    ()
);
```

**See also**

- [group\_by\_use\_nulls](/docs/operations/settings/settings#group_by_use_nulls) setting for SQL standard compatibility.

## Implementation Details[​](#implementation-details "Direct link to Implementation Details")

Aggregation is one of the most important features of a column-oriented DBMS, and thus it's implementation is one of the most heavily optimized parts of ClickHouse. By default, aggregation is done in memory using a hash-table. It has 40+ specializations that are chosen automatically depending on "grouping key" data types.

### GROUP BY Optimization Depending on Table Sorting Key[​](#group-by-optimization-depending-on-table-sorting-key "Direct link to GROUP BY Optimization Depending on Table Sorting Key")

The aggregation can be performed more effectively, if a table is sorted by some key, and `GROUP BY` expression contains at least prefix of sorting key or injective functions. In this case when a new key is read from table, the in-between result of aggregation can be finalized and sent to client. This behaviour is switched on by the [optimize\_aggregation\_in\_order](/docs/operations/settings/settings#optimize_aggregation_in_order) setting. Such optimization reduces memory usage during aggregation, but in some cases may slow down the query execution.

### GROUP BY in External Memory[​](#group-by-in-external-memory "Direct link to GROUP BY in External Memory")

You can enable dumping temporary data to the disk to restrict memory usage during `GROUP BY`.
The [max\_bytes\_before\_external\_group\_by](/docs/operations/settings/settings#max_bytes_before_external_group_by) setting determines the threshold RAM consumption for dumping `GROUP BY` temporary data to the file system. If set to 0 (the default), it is disabled.
Alternatively, you can set [max\_bytes\_ratio\_before\_external\_group\_by](/docs/operations/settings/settings#max_bytes_ratio_before_external_group_by), which allows to use `GROUP BY` in external memory only once the query reaches certain threshold of used memory.