Question

Differentiate single grouping sets from multiple grouping sets in SQL

Answer 1

By definition, a grouping set is a group of columns by which you group. Typically, a single query with an aggregate defines a single grouping set.

For EX:

Suppose we have created a table sales.sales_summary.

The following query defines a grouping set that includes brand and category which is denoted as (brand, category). The query returns the sales amount grouped by brand and category:

SELECT brand, category, SUM (sales) sales FROM sales.sales_summary GROUP BY brand, category ORDER BY brand, category;

Output:

brand category sales Electra Children Bicycles 207606.0000 Electra Comfort Bicycles 271542.0000 Electra Cruisers Bicycles 694909.0000 Electra Electric Bikes 31264.0000 Haro Children Bicycles 29240.0000 Haro Mountain Bikes 156145.0000 Heller Mountain Bikes 171459.0000 Pure Cycles Cruisers Bicycles 149476.0000 Ritchey Mountain Bikes 78899.0000 Strider Children Bicycles 4320.0000 Sun Bicycles Children Bicycles 2328.0000 Sun Bicycles Comfort Bicycles 122478.0000 Sun Bicycles Cruisers Bicycles 150647.0000 Sun Bicycles Electric Bikes 47049.0000 Sun Bicycles Mountain Bikes 19492.0000 Surly Cyclocross Bicycles 439644.0000 Surly Mountain Bikes 441383.0000 Surly Road Bikes 68478.0000 Trek Children Bicycles 48695.0000 Trek Cyclocross Bicycles 271367.0000 Trek Electric Bikes 838372.0000 Trek Mountain Bikes 1847700.0 Trek Road Bikes 1596620.0

The following query returns the sales amount by brand. It defines a grouping set (brand):

SELECT brand, SUM (sales) sales FROM sales.sales_summary GROUP BY brand ORDER BY brand; 

Output:

Here we are using a ORDER BY clause to show a single grouping set.