The GROUP BY
clause groups rows in a table with identical data, thus eliminating redundancy in the output and aggregates that apply to these groups.
COUNT
, SUM
, AVG
, etc.).
The GROUP BY
clause follows the WHERE
clause in a SELECT
statement and can precede the optional ORDER BY
clause.
Here is the basic syntax of the GROUP BY
clause:
GROUP BY
clause.
Basic GROUP BY
example:
GROUP BY GROUPING SETS()
GROUPING SETS()
option, you can group data by multiple sets of columns in a single query. Each sublist of GROUPING SETS
may specify zero or more columns or expressions and is interpreted the same way as though it were directly in the GROUP BY
clause.
With GROUPING SETS
GROUP BY ROLLUP()
GROUP BY ROLLUP()
clause extends the functionality of the GROUP BY
clause by providing a way to generate subtotals and grand totals for multiple levels of grouping. It creates a result set that includes rows representing different combinations of the specified grouping columns, along with subtotals and a grand total.
The columns listed in the GROUP BY ROLLUP
clause define the hierarchy of grouping levels, with the rightmost column being the most detailed level of grouping.
product_category | product_subcategory | region | total_sales |
---|---|---|---|
Electronics | Smartphones | North | 1000 |
Electronics | Smartphones | South | 1500 |
Electronics | Smartphones | NULL | 2500 |
Electronics | Laptops | North | 2000 |
Electronics | Laptops | South | 0 |
Electronics | Laptops | NULL | 2000 |
Electronics | NULL | NULL | 4500 |
Furniture | Chairs | North | 500 |
Furniture | Chairs | South | 700 |
Furniture | Chairs | NULL | 1200 |
Furniture | Tables | North | 1000 |
Furniture | Tables | South | 1200 |
Furniture | Tables | NULL | 2200 |
Furniture | NULL | NULL | 3400 |
NULL | NULL | NULL | 7900 |
product_category
, product_subcategory
, and region
. The NULL values in the grouping columns represent the grand total.
GROUP BY CUBE()
GROUP BY CUBE()
clause generates multiple levels of grouping within a single query. It creates a result set that includes all possible combinations of the specified columns, generating a subtotal for each combination.
The GROUP BY CUBE()
clause is particularly useful when you need to analyze data across multiple dimensions and want to examine various levels of aggregation simultaneously.
GROUP BY CUBE clause