How To Use Grouping Set In SQL With Grouping Function

Introduction

Here, we’ll go over how to use the group by and group function in a grouping set.

Setup a sales summary table

For the demonstration, let’s create a new table with the name sales.sales summary.

SELECT
    b.brand_name AS brand,
    c.category_name AS category,
    p.model_year,
    round(
        SUM (
            quantity * i.list_price * (1 - discount)
        ),
        0
    ) sales INTO sales.sales_summary
FROM
    sales.order_items i
INNER JOIN production.products p ON p.product_id = i.product_id
INNER JOIN production.brands b ON b.brand_id = p.brand_id
INNER JOIN production.categories c ON c.category_id = p.category_id
GROUP BY
    b.brand_name,
    c.category_name,
    p.model_year
ORDER BY
    b.brand_name,
    c.category_name,
    p.model_year;

By brand and category, we retrieve the sales amount information in this query and add it to the sales.sales summary table.

The sales.sales summary table responds to the following query with data:

SELECT
  *
FROM
  sales.sales_summary
ORDER BY
  brand,
  category,
  model_year;

Introduction to SQL Server GROUPING SETS

A grouping set is a set of columns that you can categorise by definition. Typically, one grouping set is defined by a single query with an aggregate.

For instance, the query that follows designates a grouping set that includes brand and category as (brand, category). The search yields the number of sales broken down by brand and category:

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

The sales amount broken down by brand is given by the next inquiry. It establishes a series of groupings (brand):

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

An empty grouping set is defined with the following query (). All brands and categories’ sales totals are returned.

SELECT
    SUM (sales) sales
FROM
    sales.sales_summary;

The four aforementioned queries produce four result sets and four grouping sets:

(brand, category)
(brand)
(category)
()

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

As predicted, the query returned a single response containing aggregates for all grouping sets.

It does, however, have two main flaws:

The question is rather long.
Because SQL Server must conduct four subqueries and merge the result sets into a single one, the query is sluggish.
SQL Server includes a sub clause of the GROUP BY clause called GROUPING SETS to address these issues.

Multiple grouping sets are defined using GROUPING SETS in the same query. The general syntax of the GROUPING SETS is shown below:

SELECT
    column1,
    column2,
    aggregate_function (column3)
FROM
    table_name
GROUP BY
    GROUPING SETS (
        (column1, column2),
        (column1),
        (column2),
        ()
);

This query creates four grouping sets:

(column1,column2)
(column1)
(column2)
()

You may rebuild the query that collects the sales data using this GROUPING SETS:

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

As you can see, the query produces the same result as the one that uses the UNION ALL operator. This query, on the other hand, is considerably more readable and, of course, more efficient.

Thank You.