General functions

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.

> 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.

> 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.

> 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

> 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.

SELECT ...
EXCEPT
SELECT ...

> 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

Query Engine

Get started

Connect data

Run clusters

Query

Connect your tools

Secure and govern

Troubleshoot

Reference

Concepts

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

ROLLUP ( GROUP BY ROLLUP <groupRollup> )

CUBE (GROUP BY CUBE <groupCube>)

EXCEPT ( <column> )

EXCEPT SET OPERATOR

​GROUPING SETS ( GROUP BY GROUPING SETS (<groupsets>) )

​ROLLUP ( GROUP BY ROLLUP <groupRollup> )

​CUBE (GROUP BY CUBE <groupCube>)

​EXCEPT ( <column> )

​EXCEPT SET OPERATOR

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

ROLLUP ( GROUP BY ROLLUP <groupRollup> )

CUBE (GROUP BY CUBE <groupCube>)

EXCEPT ( <column> )

EXCEPT SET OPERATOR