{"id":742,"date":"2018-08-05T08:18:53","date_gmt":"2018-08-05T01:18:53","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=742"},"modified":"2024-04-03T14:45:06","modified_gmt":"2024-04-03T07:45:06","slug":"sql-server-cube","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-cube\/","title":{"rendered":"SQL Server CUBE"},"content":{"rendered":"\n

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

Introduction to SQL Server CUBE #<\/a><\/h2>\n\n\n\n

Grouping sets<\/a> specify groupings of data in a single query<\/a>. For example, the following query defines a single grouping set represented as (brand):<\/p>\n\n\n

SELECT<\/span> \n    brand, \n    SUM<\/span>(sales)\nFROM<\/span> \n    sales.sales_summary\nGROUP<\/span> BY<\/span> \n    brand;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\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;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> \n    SUM<\/span>(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
SELECT<\/span>\n    d1,\n    d2,\n    d3,\n    aggregate_function (c4)\nFROM<\/span>\n    table_name\nGROUP<\/span> BY<\/span>\n    CUBE<\/span> (d1, d2, d3);     <\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the CUBE<\/code> generates all possible grouping sets based on the dimension columns d1, d2, and d3 that you specify in the CUBE<\/code> clause.<\/p>\n\n\n\n
SELECT<\/span>\n    d1,\n    d2,\n    d3,\n    aggregate_function (c4)\nFROM<\/span>\n    table_name\nGROUP<\/span> BY<\/span>\n    GROUPING<\/span> SETS<\/span> (\n        (d1,d2,d3), \n        (d1,d2),\n        (d1,d3),\n        (d2,d3),\n        (d1),\n        (d2),\n        (d3), \n        ()\n     );<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you have N<\/code> dimension columns specified in the CUBE<\/code>, you will have 2N<\/sup> grouping sets.<\/p>\n\n\n\n
It is possible to reduce the number of grouping sets by using the CUBE<\/code> partially as shown in the following query:<\/p>\n\n\n
SELECT<\/span>\n    d1,\n    d2,\n    d3,\n    aggregate_function (c4)\nFROM<\/span>\n    table_name\nGROUP<\/span> BY<\/span>\n    d1,\n    CUBE<\/span> (d2, d3);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this case, the query generates four grouping sets because there are only two dimension columns specified in the CUBE<\/code>.<\/p>\n\n\n\n
SQL Server CUBE examples #<\/a><\/h2>\n\n\n\n
The following statement uses the CUBE<\/code> to generate four grouping sets:<\/p>\n\n\n\n
    \n
  1. (brand, category)<\/li>\n\n\n\n
  2. (brand)<\/li>\n\n\n\n
  3. (category)<\/li>\n\n\n\n
  4. ()<\/li>\n<\/ol>\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    CUBE<\/span>(brand, category<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Here is the output:<\/p>\n\n\n\n
    \"SQL<\/figure>\n\n\n\n
    In this example, we have two dimension columns specified in the CUBE<\/code> clause, therefore, we have a total of four grouping sets.<\/p>\n\n\n\n
    The following example illustrates how to perform a partial CUBE<\/code> to reduce the number of grouping sets generated by the query:<\/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    CUBE<\/span>(category<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    The following picture shows the output:<\/p>\n\n\n\n
    \"SQL<\/figure>\n\n\n\n
    In this tutorial, you have learned how to use the SQL Server CUBE<\/code> to generate multiple grouping sets.<\/p>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
The above query returns the same result set as the following query, which uses the  GROUPING SETS<\/a><\/code>:<\/p>\n\n\n
The CUBE<\/code> is a subclause of the GROUP BY<\/a><\/code> clause that allows you to generate multiple grouping sets. The following illustrates the general syntax of the CUBE<\/code>:<\/p>\n\n\n
Even though the following query does not use the GROUP BY<\/a><\/code> clause, it generates an empty grouping set which is denoted as ().<\/p>\n\n\n
If you have not followed the GROUPING SETS<\/a><\/code> tutorial, you can create the sales.sales_summary<\/code> table by using the following query:<\/p>\n\n\n