{"id":654,"date":"2018-06-09T22:15:39","date_gmt":"2018-06-09T15:15:39","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=654"},"modified":"2024-04-03T14:46:59","modified_gmt":"2024-04-03T07:46:59","slug":"sql-server-grouping-sets","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-grouping-sets\/","title":{"rendered":"SQL Server GROUPING SETS"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server GROUPING SETS<\/code> to generate multiple grouping sets.<\/p>\n\n\n\n

Setup a sales summary table #<\/a><\/h2>\n\n\n\n

Let’s create a new table<\/a> named sales.sales_summary<\/code> for the demonstration.<\/p>\n\n\n

SELECT<\/span>\n    b.brand_name AS<\/span> brand,\n    c.category_name AS<\/span> category<\/span>,\n    p.model_year,\n    round<\/span>(\n        SUM<\/span> (\n            quantity * i.list_price * (1<\/span> - discount)\n        ),\n        0<\/span>\n    ) sales INTO<\/span> sales.sales_summary\nFROM<\/span>\n    sales.order_items i\nINNER<\/span> JOIN<\/span> production.products p ON<\/span> p.product_id = i.product_id\nINNER<\/span> JOIN<\/span> production.brands b ON<\/span> b.brand_id = p.brand_id\nINNER<\/span> JOIN<\/span> production.categories c ON<\/span> c.category_id = p.category_id\nGROUP<\/span> BY<\/span>\n    b.brand_name,\n    c.category_name,\n    p.model_year\nORDER<\/span> BY<\/span>\n    b.brand_name,\n    c.category_name,\n    p.model_year;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this query, we retrieve the sales amount data by brand and category and populate it into the sales.sales_summary<\/code> table.<\/p>\n\n\n\n
The following query returns data from the sales.sales_summary<\/code> table:<\/p>\n\n\n
SELECT<\/span>\n\t*\nFROM<\/span>\n\tsales.sales_summary\nORDER<\/span> BY<\/span>\n\tbrand,\n\tcategory<\/span>,\n\tmodel_year;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
Getting started with SQL Server GROUPING SETS #<\/a><\/h2>\n\n\n\n
By definition, a grouping set is a group of columns by which you group. Typically, a single query with an aggregate<\/a> defines a single grouping set.<\/p>\n\n\n\n
For example, the following query defines a grouping set that includes brand and category which is denoted as (brand, category)<\/code>. The query returns the sales amount grouped by brand and category:<\/p>\n\n\n
SELECT<\/span>\n    brand,\n    category<\/span>,\n    SUM<\/span> (sales) sales\nFROM<\/span>\n    sales.sales_summary\nGROUP<\/span> BY<\/span>\n    brand,\n    category<\/span>\nORDER<\/span> BY<\/span>\n    brand,\n    category<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"\"<\/figure>\n\n\n\n
The following query returns the sales amount by brand. It defines a grouping set (brand)<\/code>:<\/p>\n\n\n
SELECT<\/span>\n    brand,\n    SUM<\/span> (sales) sales\nFROM<\/span>\n    sales.sales_summary\nGROUP<\/span> BY<\/span>\n    brand\nORDER<\/span> BY<\/span>\n    brand;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
The following query returns the sales amount by category. It defines a grouping set (category)<\/code>:<\/p>\n\n\n
SELECT<\/span>\n    category<\/span>,\n    SUM<\/span> (sales) sales\nFROM<\/span>\n    sales.sales_summary\nGROUP<\/span> BY<\/span>\n    category<\/span>\nORDER<\/span> BY<\/span>\n    category<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
The following query defines an empty grouping set ()<\/code>. It returns the sales amount for all brands and categories.<\/p>\n\n\n
SELECT<\/span>\n    SUM<\/span> (sales) sales\nFROM<\/span>\n    sales.sales_summary;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"\"<\/figure>\n\n\n\n
The four queries above return four result sets with four grouping sets:<\/p>\n\n\n
(brand, category)\n(brand)\n(category)\n()\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To get a unified result set with the aggregated data for all grouping sets, you can use the UNION ALL<\/a><\/code> operator.<\/p>\n\n\n\n
Because UNION ALL<\/code> operator requires all result sets to have the same number of columns, you need to add NULL to the select list of the queries like this:<\/p>\n\n\n
SELECT<\/span>\n    brand,\n    category<\/span>,\n    SUM<\/span> (sales) sales\nFROM<\/span>\n    sales.sales_summary\nGROUP<\/span> BY<\/span>\n    brand,\n    category<\/span>\nUNION<\/span> ALL<\/span>\nSELECT<\/span>\n    brand,\n    NULL<\/span>,\n    SUM<\/span> (sales) sales\nFROM<\/span>\n    sales.sales_summary\nGROUP<\/span> BY<\/span>\n    brand\nUNION<\/span> ALL<\/span>\nSELECT<\/span>\n    NULL<\/span>,\n    category<\/span>,\n    SUM<\/span> (sales) sales\nFROM<\/span>\n    sales.sales_summary\nGROUP<\/span> BY<\/span>\n    category<\/span>\nUNION<\/span> ALL<\/span>\nSELECT<\/span>\n    NULL<\/span>,\n    NULL<\/span>,\n    SUM<\/span> (sales)\nFROM<\/span>\n    sales.sales_summary\nORDER<\/span> BY<\/span> brand, category<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
The query generated a single result with the aggregates for all grouping sets as we expected.<\/p>\n\n\n\n
However, it has two major problems:<\/p>\n\n\n\n
    \n
  1. The query is quite lengthy.<\/li>\n\n\n\n
  2. The query is slow because the SQL Server needs to execute four subqueries and combine the result sets into a single one.<\/li>\n<\/ol>\n\n\n\n
    To fix these problems, SQL Server provides a subclause of the GROUP BY<\/a><\/code> clause called GROUPING SETS<\/code>.<\/p>\n\n\n\n
    The GROUPING SETS<\/code> defines multiple grouping sets in the same query. The following shows the general syntax of the GROUPING SETS<\/code>:<\/p>\n\n\n
    SELECT<\/span>\n    column1,\n    column2,\n    aggregate_function (column3)\nFROM<\/span>\n    table_name\nGROUP<\/span> BY<\/span>\n    GROUPING<\/span> SETS<\/span> (\n        (column1, column2),\n        (column1),\n        (column2),\n        ()\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    This query creates four grouping sets:<\/p>\n\n\n
    (column1,column2)\n(column1)\n(column2)\n()\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    You can use this GROUPING SETS<\/code> to rewrite the query that gets the sales data as follows:<\/p>\n\n\n
    SELECT<\/span>\n\tbrand,\n\tcategory<\/span>,\n\tSUM<\/span> (sales) sales\nFROM<\/span>\n\tsales.sales_summary\nGROUP<\/span> BY<\/span>\n\tGROUPING<\/span> SETS<\/span> (\n\t\t(brand, category<\/span>),\n\t\t(brand),\n\t\t(category<\/span>),\n\t\t()\n\t)\nORDER<\/span> BY<\/span>\n\tbrand,\n\tcategory<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    As you can see, the query produces the same result as the one that uses the UNION ALL<\/code> operator. However, this query is much more readable and of course more efficient.<\/p>\n\n\n\n
    GROUPING function #<\/a><\/h2>\n\n\n\n
    The GROUPING<\/code> function indicates whether a specified column in a GROUP BY<\/code> clause is aggregated or not. It returns 1 for aggregated or 0 for not aggregated in the result set.<\/p>\n\n\n\n
    See the following query example:<\/p>\n\n\n
    SELECT<\/span>\n    GROUPING<\/span>(brand) grouping_brand,\n    GROUPING<\/span>(category<\/span>) grouping_category,\n    brand,\n    category<\/span>,\n    SUM<\/span> (sales) sales\nFROM<\/span>\n    sales.sales_summary\nGROUP<\/span> BY<\/span>\n    GROUPING<\/span> SETS<\/span> (\n        (brand, category<\/span>),\n        (brand),\n        (category<\/span>),\n        ()\n    )\nORDER<\/span> BY<\/span>\n    brand,\n    category<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    \"SQL<\/figure>\n\n\n\n
    The value in the grouping_brand<\/code>\u00a0column indicates whether the row is aggregated or not:<\/p>\n\n\n\n