> ## Documentation Index
> Fetch the complete documentation index at: https://docs.e6data.com/llms.txt
> Use this file to discover all available pages before exploring further.

# General functions

> Commonly used functions for data processing.

#### GROUPING SETS ( GROUP BY GROUPING SETS (\<groupsets>) )

`GROUP BY GROUPING SETS` is an advanced extension of the `GROUP BY` clause that allows multiple groupings within a single statement. Each grouping set is a set of dimension columns.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
> select count(cc_company) as total_count, cc_county, cc_class
from call_center
where cc_county in ('Williamson County', 'Bronx County', 'Huron County')
group by grouping sets (cc_county, cc_class);

# | total_count  | cc_county         | cc_class

1 |     2        | Williamson County |	null
2 |     3        | Bronx County      |	null
3 |     1        | Huron County      |	null
4 |     2        | null              |	large
5 |     4        | null  	     |	medium
```

#### ROLLUP ( GROUP BY ROLLUP \<groupRollup> )

`GROUP BY ROLLUP` is an extension of the `GROUP BY` clause that produces sub-total rows (in addition to the grouped rows). Sub-total rows are rows that further aggregate whose values are derived by computing the same aggregate functions that were used to produce the grouped rows.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
> select count(cc_company) as total_count, cc_county, cc_class
from call_center
where cc_county in ('Williamson County', 'Bronx County', 'Huron County')
group by rollup (cc_county, cc_class);

# | total_count  | cc_county         | cc_class

1 |     2        | Williamson County |	null
2 |     3        | Bronx County      |	null
3 |     1        | Huron County      |	null
4 |     2        | Williamson County |	large
5 |     3        | Bronx County      |  medium
6 |     1        | Huron County      |	medium
7 |     6        | null  	     |	null
```

#### CUBE (GROUP BY CUBE \<groupCube>)

`GROUP BY CUBE` is an extension of the `GROUP BY` clause similar to `GROUP BY ROLLUP`. In addition to producing all the rows of a `GROUP BY ROLLUP`, `GROUP BY CUBE` adds all the “cross-tabulations” rows. Sub-total rows are rows that further aggregate whose values are derived by computing the same aggregate functions that were used to produce the grouped rows.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
> select count(cc_company) AS total_count, cc_county, cc_class
  from call_center
  where cc_county in ('Williamson County', 'Bronx County', 'Huron County')
  group by cube (cc_county, cc_class);

# | total_count | cc_county         | cc_class
----------------------------------------------
1 | 6          | null              | null
2 | 2          | Williamson County | null
3 | 4          | Bronx County      | null
4 | 2          | Huron County      | null
5 | 3          | Williamson County | large
6 | 1          | Bronx County      | medium
7 | 2          | Huron County      | medium
8 | 3          | null              | large
9 | 1          | null              | medium
```

#### EXCEPT ( \<column> )

`SELECT * EXCEPT` *columns that should be excluded from the results*

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
> WITH cte AS (SELECT 5 as c1,'e6data' as c2, 10 as c3)
  SELECT * EXCEPT (c3, c2) FROM cte

c1
5
```

#### EXCEPT SET OPERATOR

Eliminates rows from the result set of one query that are present in the result set of another query, while also removing duplicates.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT ...
EXCEPT
SELECT ...
```

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
> with cte as
    (select 1 as id, 'AAAA' as name
    union all
    select 2 as id, 'AAAB' as name
    union all
    select 3 as id, 'AAAC' as name
    union all
    select 4 as id, 'AAAD' as name
    )
    select * from cte
    except
    select * from cte
    where id < 3
    order by 1,2

|id   |name
|3    |AAAC
|4    |AAAD
```


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.