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.
>selectcount(cc_company) as total_count, cc_county, cc_classfrom call_centerwhere cc_county in ('Williamson County', 'Bronx County', 'Huron County')group by grouping sets (cc_county, cc_class);# | total_count | cc_county | cc_class1 | 2 | Williamson County | null2 | 3 | Bronx County | null3 | 1 | Huron County | null4 | 2 | null | large5 | 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.
>selectcount(cc_company) as total_count, cc_county, cc_classfrom call_centerwhere cc_county in ('Williamson County', 'Bronx County', 'Huron County')group byrollup (cc_county, cc_class);# | total_count | cc_county | cc_class1 | 2 | Williamson County | null2 | 3 | Bronx County | null3 | 1 | Huron County | null4 | 2 | Williamson County | large5 | 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.
>selectcount(cc_company) AS total_count, cc_county, cc_classfrom call_centerwhere cc_county in ('Williamson County', 'Bronx County', 'Huron County')group bycube (cc_county, cc_class);# | total_count | cc_county | cc_class----------------------------------------------1 | 6 | null | null2 | 2 | Williamson County | null3 | 4 | Bronx County | null4 | 2 | Huron County | null5 | 3 | Williamson County | large6 | 1 | Bronx County | medium7 | 2 | Huron County | medium8 | 3 | null | large9 | 1 | null | medium
EXCEPT ( <column> )
SELECT * EXCEPTcolumns that should be excluded from the results
>WITH cte AS (SELECT5as c1,'e6data'as c2, 10as c3)SELECT*EXCEPT (c3, c2) FROM ctec25
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 ... EXCEPTSELECT ...
>with cte as (select1as id, 'AAAA'asnameunion allselect2as id, 'AAAB'asnameunion allselect3as id, 'AAAC'asnameunion allselect4as id, 'AAAD'asname )select*from cteexceptselect*from cte where id <3order by1,2|id |name|3 |AAAC|4 |AAAD