🕷️ Crawler Inspector

URL Lookup

Direct Parameter Lookup

Raw Queries and Responses

1. Shard Calculation

Query:

Response:

Calculated Shard: 89 (from laksa086)

2. Crawled Status Check

Query:

curl -X POST \
  'http://laksa089.int.ahrefs:8124/' \
  -H 'Content-Type: text/plain' \
  -H 'X-ClickHouse-Database: crawler3' \
  -H 'Authorization: Basic YXBpOg==' \
  -d 'SELECT getAhrefsURLFromUnparsed(src_unparsed) AS found_url, ifNull(toUnixTimestamp(download_stamp), 0) AS crawl_time, ifNull(toUnixTimestamp(props_url_first_seen), 0) AS first_indexed_time, download_http_code AS http_code, src_unparsed AS src_unparsed, src_root_hash AS src_root_hash, history_drop_reason AS history_drop_reason, meta_title AS meta_title, meta_descriptions AS meta_descriptions, attrs_boilerpipe_text AS attrs_boilerpipe_text, attrs_markdown AS attrs_markdown, attrs_readable_markdown AS attrs_readable_markdown, meta_canonical AS meta_canonical FROM crawler3.page_info_local FINAL PREWHERE (src_root_hash, src_unparsed) IN ((getAhrefsRootHashFromUnparsed(getAhrefsUnparsedNoserviceFromURL(\'https://clickhouse.com/docs/sql-reference/aggregate-functions/grouping_function\')), getAhrefsUnparsedNoserviceFromURL(\'https://clickhouse.com/docs/sql-reference/aggregate-functions/grouping_function\'))) FORMAT JSONEachRow'

Response:

{"found_url":"https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/grouping_function","crawl_time":1775979287,"first_indexed_time":1740105823,"http_code":200,"src_unparsed":"com,clickhouse!\/docs\/sql-reference\/aggregate-functions\/grouping_function s443","src_root_hash":"12633450985039531489","history_drop_reason":null,"meta_title":"GROUPING | ClickHouse Docs","meta_descriptions":["Documentation for the GROUPING aggregate function."],"attrs_boilerpipe_text":"GROUPING\n\nROLLUP\nand\nCUBE\nare modifiers to GROUP BY. Both of these calculate subtotals. ROLLUP takes an ordered list of columns, for example\n(day, month, year)\n, and calculates subtotals at each level of the aggregation and then a grand total. CUBE calculates subtotals across all possible combinations of the columns specified. GROUPING identifies which rows returned by ROLLUP or CUBE are superaggregates, and which are rows that would be returned by an unmodified GROUP BY.\nThe GROUPING function takes multiple columns as an argument, and returns a bitmask.\n1\nindicates that a row returned by a\nROLLUP\nor\nCUBE\nmodifier to\nGROUP BY\nis a subtotal\n0\nindicates that a row returned by a\nROLLUP\nor\nCUBE\nis a row that is not a subtotal\nGROUPING SETS\n\nBy default, the CUBE modifier calculates subtotals for all possible combinations of the columns passed to CUBE. GROUPING SETS allows you to specify the specific combinations to calculate.\nAnalyzing hierarchical data is a good use case for ROLLUP, CUBE, and GROUPING SETS modifiers.  The sample here is a table containing data  about what Linux distribution, and the version of that distribution is installed across two datacenters.  It may be valuable to look at the data by distribution, version, and location.\nLoad sample data\n\nCREATE\nTABLE\nservers\n(\ndatacenter\nVARCHAR\n(\n255\n)\n,\ndistro\nVARCHAR\n(\n255\n)\nNOT\nNULL\n,\nversion\nVARCHAR\n(\n50\n)\nNOT\nNULL\n,\nquantity\nINT\n)\nORDER\nBY\n(\ndatacenter\n,\ndistro\n,\nversion\n)\nINSERT\nINTO\nservers\n(\ndatacenter\n,\ndistro\n,\nversion\n,\nquantity\n)\nVALUES\n(\n'Schenectady'\n,\n'Arch'\n,\n'2022.08.05'\n,\n50\n)\n,\n(\n'Westport'\n,\n'Arch'\n,\n'2022.08.05'\n,\n40\n)\n,\n(\n'Schenectady'\n,\n'Arch'\n,\n'2021.09.01'\n,\n30\n)\n,\n(\n'Westport'\n,\n'Arch'\n,\n'2021.09.01'\n,\n20\n)\n,\n(\n'Schenectady'\n,\n'Arch'\n,\n'2020.05.01'\n,\n10\n)\n,\n(\n'Westport'\n,\n'Arch'\n,\n'2020.05.01'\n,\n5\n)\n,\n(\n'Schenectady'\n,\n'RHEL'\n,\n'9'\n,\n60\n)\n,\n(\n'Westport'\n,\n'RHEL'\n,\n'9'\n,\n70\n)\n,\n(\n'Westport'\n,\n'RHEL'\n,\n'7'\n,\n80\n)\n,\n(\n'Schenectady'\n,\n'RHEL'\n,\n'7'\n,\n80\n)\nSELECT\n*\nFROM\nservers\n;\n┌─datacenter──┬─distro─┬─version────┬─quantity─┐\n│ Schenectady │ Arch   │ 2020.05.01 │       10 │\n│ Schenectady │ Arch   │ 2021.09.01 │       30 │\n│ Schenectady │ Arch   │ 2022.08.05 │       50 │\n│ Schenectady │ RHEL   │ 7          │       80 │\n│ Schenectady │ RHEL   │ 9          │       60 │\n│ Westport    │ Arch   │ 2020.05.01 │        5 │\n│ Westport    │ Arch   │ 2021.09.01 │       20 │\n│ Westport    │ Arch   │ 2022.08.05 │       40 │\n│ Westport    │ RHEL   │ 7          │       80 │\n│ Westport    │ RHEL   │ 9          │       70 │\n└─────────────┴────────┴────────────┴──────────┘\n10 rows in set. Elapsed: 0.409 sec.\nSimple queries\n\nGet the count of servers in each data center by distribution:\nSELECT\ndatacenter\n,\ndistro\n,\nSUM\n(\nquantity\n)\nqty\nFROM\nservers\nGROUP\nBY\ndatacenter\n,\ndistro\n;\n┌─datacenter──┬─distro─┬─qty─┐\n│ Schenectady │ RHEL   │ 140 │\n│ Westport    │ Arch   │  65 │\n│ Schenectady │ Arch   │  90 │\n│ Westport    │ RHEL   │ 150 │\n└─────────────┴────────┴─────┘\n4 rows in set. Elapsed: 0.212 sec.\nSELECT\ndatacenter\n,\nSUM\n(\nquantity\n)\nqty\nFROM\nservers\nGROUP\nBY\ndatacenter\n;\n┌─datacenter──┬─qty─┐\n│ Westport    │ 215 │\n│ Schenectady │ 230 │\n└─────────────┴─────┘\n2 rows in set. Elapsed: 0.277 sec.\nSELECT\ndistro\n,\nSUM\n(\nquantity\n)\nqty\nFROM\nservers\nGROUP\nBY\ndistro\n;\n┌─distro─┬─qty─┐\n│ Arch   │ 155 │\n│ RHEL   │ 290 │\n└────────┴─────┘\n2 rows in set. Elapsed: 0.352 sec.\nSELECT\nSUM\n(\nquantity\n)\nqty\nFROM\nservers\n;\n┌─qty─┐\n│ 445 │\n└─────┘\n1 row in set. Elapsed: 0.244 sec.\nComparing multiple GROUP BY statements with GROUPING SETS\n\nBreaking down the data without CUBE, ROLLUP, or GROUPING SETS:\nSELECT\ndatacenter\n,\ndistro\n,\nSUM\n(\nquantity\n)\nqty\nFROM\nservers\nGROUP\nBY\ndatacenter\n,\ndistro\nUNION\nALL\nSELECT\ndatacenter\n,\nnull\n,\nSUM\n(\nquantity\n)\nqty\nFROM\nservers\nGROUP\nBY\ndatacenter\nUNION\nALL\nSELECT\nnull\n,\ndistro\n,\nSUM\n(\nquantity\n)\nqty\nFROM\nservers\nGROUP\nBY\ndistro\nUNION\nALL\nSELECT\nnull\n,\nnull\n,\nSUM\n(\nquantity\n)\nqty\nFROM\nservers\n;\n┌─datacenter─┬─distro─┬─qty─┐\n│ ᴺᵁᴸᴸ       │ ᴺᵁᴸᴸ   │ 445 │\n└────────────┴────────┴─────┘\n┌─datacenter──┬─distro─┬─qty─┐\n│ Westport    │ ᴺᵁᴸᴸ   │ 215 │\n│ Schenectady │ ᴺᵁᴸᴸ   │ 230 │\n└─────────────┴────────┴─────┘\n┌─datacenter──┬─distro─┬─qty─┐\n│ Schenectady │ RHEL   │ 140 │\n│ Westport    │ Arch   │  65 │\n│ Schenectady │ Arch   │  90 │\n│ Westport    │ RHEL   │ 150 │\n└─────────────┴────────┴─────┘\n┌─datacenter─┬─distro─┬─qty─┐\n│ ᴺᵁᴸᴸ       │ Arch   │ 155 │\n│ ᴺᵁᴸᴸ       │ RHEL   │ 290 │\n└────────────┴────────┴─────┘\n9 rows in set. Elapsed: 0.527 sec.\nGetting the same information using GROUPING SETS:\nSELECT\ndatacenter\n,\ndistro\n,\nSUM\n(\nquantity\n)\nqty\nFROM\nservers\nGROUP\nBY\nGROUPING SETS\n(\n(\ndatacenter\n,\ndistro\n)\n,\n(\ndatacenter\n)\n,\n(\ndistro\n)\n,\n(\n)\n)\n┌─datacenter──┬─distro─┬─qty─┐\n│ Schenectady │ RHEL   │ 140 │\n│ Westport    │ Arch   │  65 │\n│ Schenectady │ Arch   │  90 │\n│ Westport    │ RHEL   │ 150 │\n└─────────────┴────────┴─────┘\n┌─datacenter──┬─distro─┬─qty─┐\n│ Westport    │        │ 215 │\n│ Schenectady │        │ 230 │\n└─────────────┴────────┴─────┘\n┌─datacenter─┬─distro─┬─qty─┐\n│            │        │ 445 │\n└────────────┴────────┴─────┘\n┌─datacenter─┬─distro─┬─qty─┐\n│            │ Arch   │ 155 │\n│            │ RHEL   │ 290 │\n└────────────┴────────┴─────┘\n9 rows in set. Elapsed: 0.427 sec.\nComparing CUBE with GROUPING SETS\n\nThe CUBE in the next query,\nCUBE(datacenter,distro,version)\nprovides a hierarchy that may not make sense.  It does not make sense to look at Version across the two distributions (as Arch and RHEL do not have the same release cycle or version naming standards).  The GROUPING SETS example following this one is more appropriate as it groups\ndistro\nand\nversion\nin the same set.\nSELECT\ndatacenter\n,\ndistro\n,\nversion\n,\nSUM\n(\nquantity\n)\nFROM\nservers\nGROUP\nBY\nCUBE\n(\ndatacenter\n,\ndistro\n,\nversion\n)\nORDER\nBY\ndatacenter\n,\ndistro\n;\n┌─datacenter──┬─distro─┬─version────┬─sum(quantity)─┐\n│             │        │ 7          │           160 │\n│             │        │ 2020.05.01 │            15 │\n│             │        │ 2021.09.01 │            50 │\n│             │        │ 2022.08.05 │            90 │\n│             │        │ 9          │           130 │\n│             │        │            │           445 │\n│             │ Arch   │ 2021.09.01 │            50 │\n│             │ Arch   │ 2022.08.05 │            90 │\n│             │ Arch   │ 2020.05.01 │            15 │\n│             │ Arch   │            │           155 │\n│             │ RHEL   │ 9          │           130 │\n│             │ RHEL   │ 7          │           160 │\n│             │ RHEL   │            │           290 │\n│ Schenectady │        │ 9          │            60 │\n│ Schenectady │        │ 2021.09.01 │            30 │\n│ Schenectady │        │ 7          │            80 │\n│ Schenectady │        │ 2022.08.05 │            50 │\n│ Schenectady │        │ 2020.05.01 │            10 │\n│ Schenectady │        │            │           230 │\n│ Schenectady │ Arch   │ 2022.08.05 │            50 │\n│ Schenectady │ Arch   │ 2021.09.01 │            30 │\n│ Schenectady │ Arch   │ 2020.05.01 │            10 │\n│ Schenectady │ Arch   │            │            90 │\n│ Schenectady │ RHEL   │ 7          │            80 │\n│ Schenectady │ RHEL   │ 9          │            60 │\n│ Schenectady │ RHEL   │            │           140 │\n│ Westport    │        │ 9          │            70 │\n│ Westport    │        │ 2020.05.01 │             5 │\n│ Westport    │        │ 2022.08.05 │            40 │\n│ Westport    │        │ 7          │            80 │\n│ Westport    │        │ 2021.09.01 │            20 │\n│ Westport    │        │            │           215 │\n│ Westport    │ Arch   │ 2020.05.01 │             5 │\n│ Westport    │ Arch   │ 2021.09.01 │            20 │\n│ Westport    │ Arch   │ 2022.08.05 │            40 │\n│ Westport    │ Arch   │            │            65 │\n│ Westport    │ RHEL   │ 9          │            70 │\n│ Westport    │ RHEL   │ 7          │            80 │\n│ Westport    │ RHEL   │            │           150 │\n└─────────────┴────────┴────────────┴───────────────┘\n39 rows in set. Elapsed: 0.355 sec.\nNote\nVersion in the above example may not make sense when it is not associated with a distro, if we were tracking the kernel version it might make sense because the kernel version can be associated with either distro.  Using GROUPING SETS, as in the next example, may be a better choice.\nSELECT\ndatacenter\n,\ndistro\n,\nversion\n,\nSUM\n(\nquantity\n)\nFROM\nservers\nGROUP\nBY\nGROUPING SETS\n(\n(\ndatacenter\n,\ndistro\n,\nversion\n)\n,\n(\ndatacenter\n,\ndistro\n)\n)\n┌─datacenter──┬─distro─┬─version────┬─sum(quantity)─┐\n│ Westport    │ RHEL   │ 9          │            70 │\n│ Schenectady │ Arch   │ 2022.08.05 │            50 │\n│ Schenectady │ Arch   │ 2021.09.01 │            30 │\n│ Schenectady │ RHEL   │ 7          │            80 │\n│ Westport    │ Arch   │ 2020.05.01 │             5 │\n│ Westport    │ RHEL   │ 7          │            80 │\n│ Westport    │ Arch   │ 2021.09.01 │            20 │\n│ Westport    │ Arch   │ 2022.08.05 │            40 │\n│ Schenectady │ RHEL   │ 9          │            60 │\n│ Schenectady │ Arch   │ 2020.05.01 │            10 │\n└─────────────┴────────┴────────────┴───────────────┘\n┌─datacenter──┬─distro─┬─version─┬─sum(quantity)─┐\n│ Schenectady │ RHEL   │         │           140 │\n│ Westport    │ Arch   │         │            65 │\n│ Schenectady │ Arch   │         │            90 │\n│ Westport    │ RHEL   │         │           150 │\n└─────────────┴────────┴─────────┴───────────────┘\n14 rows in set. Elapsed: 1.036 sec.","attrs_markdown":"[Skip to main content](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/grouping_function#__docusaurus_skipToContent_fallback)\n\n[![ClickHouse](https:\/\/clickhouse.com\/docs\/img\/ch_logo_docs.svg)](https:\/\/clickhouse.com\/)\n\n- [Products](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/grouping_function)\n  \n  - [ClickHouse Cloud Best way to use ClickHouse. Available on AWS, GCP, and Azure.](https:\/\/clickhouse.com\/cloud)\n  - [BYOC (Bring Your Own Cloud) The fully managed ClickHouse Cloud service, Can be deployed in your AWS account.](https:\/\/clickhouse.com\/cloud\/bring-your-own-cloud)\n  - [ClickHouse Set up a database with open-source ClickHouse. ClickHouse](https:\/\/clickhouse.com\/clickhouse)\n  - [Discover more than 100 integrations.](https:\/\/clickhouse.com\/integrations)\n  \n  [Discover more than 100 integrations.](https:\/\/clickhouse.com\/integrations)\n- [Use cases](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/grouping_function)\n  \n  - [Real-time analytics](https:\/\/clickhouse.com\/use-cases\/real-time-analytics)\n  - [Machine Learning & Generative AI](https:\/\/clickhouse.com\/use-cases\/machine-learning-and-data-science)\n  - [Business Intelligence](https:\/\/clickhouse.com\/use-cases\/data-warehousing)\n  - [Logs, Events, Traces](https:\/\/clickhouse.com\/use-cases\/observability)\n  - [All use cases](https:\/\/clickhouse.com\/use-cases)\n  \n  [All use cases](https:\/\/clickhouse.com\/use-cases)\n- [Documentation](https:\/\/clickhouse.com\/docs)\n- [Resources](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/grouping_function)\n  \n  - [User stories](https:\/\/clickhouse.com\/user-stories)\n  - [Blog](https:\/\/clickhouse.com\/blog)\n  - [Events](https:\/\/clickhouse.com\/company\/events)\n  - [Learning and certification](https:\/\/clickhouse.com\/learn)\n  - [Comparison](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/grouping_function)\n    - [BigQuery](https:\/\/clickhouse.com\/comparison\/bigquery)\n    - [PostgreSQL](https:\/\/clickhouse.com\/comparison\/postgresql)\n    - [Redshift](https:\/\/clickhouse.com\/comparison\/redshift)\n    - [Rockset](https:\/\/clickhouse.com\/comparison\/rockset)\n    - [Snowflake](https:\/\/clickhouse.com\/comparison\/snowflake)\n  - [Video](https:\/\/clickhouse.com\/videos)\n  - [Demo](https:\/\/clickhouse.com\/demos)\n- [Pricing](https:\/\/clickhouse.com\/pricing)\n- [Contact](https:\/\/clickhouse.com\/company\/contact?loc=nav)\n\n[46\\.8k](https:\/\/github.com\/ClickHouse\/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav)\n\n[Search`Ctrl``K`](https:\/\/clickhouse.com\/docs\/search)\n\n[Sign in](https:\/\/console.clickhouse.cloud\/signIn?loc=docs-nav-signIn-cta&glxid=e936910f-4374-41f2-9fe9-b48f22a26d3a&pagePath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fgrouping_function&origPath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fgrouping_function&utm_ga=GA1.1.1997658363.1775979289)\n\n[Get started](https:\/\/console.clickhouse.cloud\/signUp?loc=docs-nav-signUp-cta&glxid=e936910f-4374-41f2-9fe9-b48f22a26d3a&pagePath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fgrouping_function&origPath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fgrouping_function&utm_ga=GA1.1.1997658363.1775979289)\n\n[Get started](https:\/\/clickhouse.com\/docs\/introduction-clickhouse)\n\n[Cloud](https:\/\/clickhouse.com\/docs\/cloud\/overview)\n\n[Manage data](https:\/\/clickhouse.com\/docs\/updating-data)\n\n[Server admin](https:\/\/clickhouse.com\/docs\/guides\/manage-and-deploy-index)\n\n[Reference](https:\/\/clickhouse.com\/docs\/sql-reference)\n\n[Integrations](https:\/\/clickhouse.com\/docs\/integrations)\n\n[ClickStack](https:\/\/clickhouse.com\/docs\/use-cases\/observability\/clickstack\/overview)\n\n[chDB](https:\/\/clickhouse.com\/docs\/chdb)\n\n[About](https:\/\/clickhouse.com\/docs\/about)\n\n[Knowledge Base](https:\/\/clickhouse.com\/docs\/knowledgebase)\n\n[English](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/grouping_function)\n\n- [English](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/grouping_function)\n- [日本語](https:\/\/clickhouse.com\/docs\/jp\/sql-reference\/aggregate-functions\/grouping_function)\n- [中文](https:\/\/clickhouse.com\/docs\/zh\/sql-reference\/aggregate-functions\/grouping_function)\n- [Русский](https:\/\/clickhouse.com\/docs\/ru\/sql-reference\/aggregate-functions\/grouping_function)\n- [한국어](https:\/\/clickhouse.com\/docs\/ko\/sql-reference\/aggregate-functions\/grouping_function)\n\n[Skip to main content](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/grouping_function#__docusaurus_skipToContent_fallback)\n\n[![ClickHouse](https:\/\/clickhouse.com\/docs\/img\/ch_logo_docs.svg)](https:\/\/clickhouse.com\/)\n\n- [Products](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/grouping_function)\n  \n  - [ClickHouse Cloud Best way to use ClickHouse. Available on AWS, GCP, and Azure.](https:\/\/clickhouse.com\/cloud)\n  - [BYOC (Bring Your Own Cloud) The fully managed ClickHouse Cloud service, Can be deployed in your AWS account.](https:\/\/clickhouse.com\/cloud\/bring-your-own-cloud)\n  - [ClickHouse Set up a database with open-source ClickHouse. ClickHouse](https:\/\/clickhouse.com\/clickhouse)\n  - [Discover more than 100 integrations.](https:\/\/clickhouse.com\/integrations)\n  \n  [Discover more than 100 integrations.](https:\/\/clickhouse.com\/integrations)\n- [Use cases](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/grouping_function)\n  \n  - [Real-time analytics](https:\/\/clickhouse.com\/use-cases\/real-time-analytics)\n  - [Machine Learning & Generative AI](https:\/\/clickhouse.com\/use-cases\/machine-learning-and-data-science)\n  - [Business Intelligence](https:\/\/clickhouse.com\/use-cases\/data-warehousing)\n  - [Logs, Events, Traces](https:\/\/clickhouse.com\/use-cases\/observability)\n  - [All use cases](https:\/\/clickhouse.com\/use-cases)\n  \n  [All use cases](https:\/\/clickhouse.com\/use-cases)\n- [Documentation](https:\/\/clickhouse.com\/docs)\n- [Resources](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/grouping_function)\n  \n  - [User stories](https:\/\/clickhouse.com\/user-stories)\n  - [Blog](https:\/\/clickhouse.com\/blog)\n  - [Events](https:\/\/clickhouse.com\/company\/events)\n  - [Learning and certification](https:\/\/clickhouse.com\/learn)\n  - [Comparison](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/grouping_function)\n    - [BigQuery](https:\/\/clickhouse.com\/comparison\/bigquery)\n    - [PostgreSQL](https:\/\/clickhouse.com\/comparison\/postgresql)\n    - [Redshift](https:\/\/clickhouse.com\/comparison\/redshift)\n    - [Rockset](https:\/\/clickhouse.com\/comparison\/rockset)\n    - [Snowflake](https:\/\/clickhouse.com\/comparison\/snowflake)\n  - [Video](https:\/\/clickhouse.com\/videos)\n  - [Demo](https:\/\/clickhouse.com\/demos)\n- [Pricing](https:\/\/clickhouse.com\/pricing)\n- [Contact](https:\/\/clickhouse.com\/company\/contact?loc=nav)\n\n[46\\.8k](https:\/\/github.com\/ClickHouse\/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav)\n\n[Search`Ctrl``K`](https:\/\/clickhouse.com\/docs\/search)\n\n[Sign in](https:\/\/console.clickhouse.cloud\/signIn?loc=docs-nav-signIn-cta&glxid=e936910f-4374-41f2-9fe9-b48f22a26d3a&pagePath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fgrouping_function&origPath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fgrouping_function&utm_ga=GA1.1.1997658363.1775979289)\n\n[Get started](https:\/\/console.clickhouse.cloud\/signUp?loc=docs-nav-signUp-cta&glxid=e936910f-4374-41f2-9fe9-b48f22a26d3a&pagePath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fgrouping_function&origPath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fgrouping_function&utm_ga=GA1.1.1997658363.1775979289)\n\n[Get started](https:\/\/clickhouse.com\/docs\/introduction-clickhouse)\n\n[Cloud](https:\/\/clickhouse.com\/docs\/cloud\/overview)\n\n[Manage data](https:\/\/clickhouse.com\/docs\/updating-data)\n\n[Server admin](https:\/\/clickhouse.com\/docs\/guides\/manage-and-deploy-index)\n\n[Reference](https:\/\/clickhouse.com\/docs\/sql-reference)\n\n[Integrations](https:\/\/clickhouse.com\/docs\/integrations)\n\n[ClickStack](https:\/\/clickhouse.com\/docs\/use-cases\/observability\/clickstack\/overview)\n\n[chDB](https:\/\/clickhouse.com\/docs\/chdb)\n\n[About](https:\/\/clickhouse.com\/docs\/about)\n\n[Knowledge Base](https:\/\/clickhouse.com\/docs\/knowledgebase)\n\n[English](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/grouping_function)\n\n- [English](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/grouping_function)\n- [日本語](https:\/\/clickhouse.com\/docs\/jp\/sql-reference\/aggregate-functions\/grouping_function)\n- [中文](https:\/\/clickhouse.com\/docs\/zh\/sql-reference\/aggregate-functions\/grouping_function)\n- [Русский](https:\/\/clickhouse.com\/docs\/ru\/sql-reference\/aggregate-functions\/grouping_function)\n- [한국어](https:\/\/clickhouse.com\/docs\/ko\/sql-reference\/aggregate-functions\/grouping_function)\n\n[Search`Ctrl``K`](https:\/\/clickhouse.com\/docs\/search)\n\n- [Introduction](https:\/\/clickhouse.com\/docs\/sql-reference)\n  - [Syntax](https:\/\/clickhouse.com\/docs\/sql-reference\/syntax)\n  - [Input and Output Formats](https:\/\/clickhouse.com\/docs\/sql-reference\/formats)\n  - [Data types](https:\/\/clickhouse.com\/docs\/sql-reference\/data-types)\n  - [Statements](https:\/\/clickhouse.com\/docs\/sql-reference\/statements)\n  - [Operators](https:\/\/clickhouse.com\/docs\/sql-reference\/operators)\n- [Engines](https:\/\/clickhouse.com\/docs\/engines)\n  - [Database Engines](https:\/\/clickhouse.com\/docs\/engines\/database-engines)\n  - [Table Engines](https:\/\/clickhouse.com\/docs\/engines\/table-engines)\n- [Functions](https:\/\/clickhouse.com\/docs\/sql-reference\/functions)\n  - [Regular functions](https:\/\/clickhouse.com\/docs\/sql-reference\/functions\/regular-functions)\n  - [Aggregate functions](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions)\n    - [Aggregate Functions](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/reference)\n    - [Combinators](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators)\n    - [Parametric](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/parametric-functions)\n    - [GROUPING](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/grouping_function)\n    - [Combinator examples](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/grouping_function)\n  - [Table functions](https:\/\/clickhouse.com\/docs\/sql-reference\/table-functions)\n  - [Window functions](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions)\n- [Formats](https:\/\/clickhouse.com\/docs\/interfaces\/formats)\n- [Data Lakes](https:\/\/clickhouse.com\/docs\/sql-reference\/datalakes)\n\n- [Functions](https:\/\/clickhouse.com\/docs\/sql-reference\/functions)\n- [Aggregate functions](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions)\n- GROUPING\n\n[Edit this page](https:\/\/github.com\/ClickHouse\/ClickHouse\/tree\/master\/docs\/en\/sql-reference\/aggregate-functions\/grouping_function.md)\n\n# GROUPING\n## GROUPING[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/grouping_function#grouping \"Direct link to GROUPING\")\n[ROLLUP](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/group-by#rollup-modifier) and [CUBE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/group-by#cube-modifier) are modifiers to GROUP BY. Both of these calculate subtotals. ROLLUP takes an ordered list of columns, for example `(day, month, year)`, and calculates subtotals at each level of the aggregation and then a grand total. CUBE calculates subtotals across all possible combinations of the columns specified. GROUPING identifies which rows returned by ROLLUP or CUBE are superaggregates, and which are rows that would be returned by an unmodified GROUP BY.\n\nThe GROUPING function takes multiple columns as an argument, and returns a bitmask.\n\n- `1` indicates that a row returned by a `ROLLUP` or `CUBE` modifier to `GROUP BY` is a subtotal\n- `0` indicates that a row returned by a `ROLLUP` or `CUBE` is a row that is not a subtotal\n## GROUPING SETS[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/grouping_function#grouping-sets \"Direct link to GROUPING SETS\")\nBy default, the CUBE modifier calculates subtotals for all possible combinations of the columns passed to CUBE. GROUPING SETS allows you to specify the specific combinations to calculate.\n\nAnalyzing hierarchical data is a good use case for ROLLUP, CUBE, and GROUPING SETS modifiers. The sample here is a table containing data about what Linux distribution, and the version of that distribution is installed across two datacenters. It may be valuable to look at the data by distribution, version, and location.\n\n### Load sample data[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/grouping_function#load-sample-data \"Direct link to Load sample data\")\n```\nCREATE TABLE servers ( datacenter VARCHAR(255),\n                         distro VARCHAR(255) NOT NULL,\n                         version VARCHAR(50) NOT NULL,\n                         quantity INT\n                       )\n                        ORDER BY (datacenter, distro, version)\n```\n```\nINSERT INTO servers(datacenter, distro, version, quantity)\nVALUES ('Schenectady', 'Arch','2022.08.05',50),\n       ('Westport', 'Arch','2022.08.05',40),\n       ('Schenectady','Arch','2021.09.01',30),\n       ('Westport', 'Arch','2021.09.01',20),\n       ('Schenectady','Arch','2020.05.01',10),\n       ('Westport', 'Arch','2020.05.01',5),\n       ('Schenectady','RHEL','9',60),\n       ('Westport','RHEL','9',70),\n       ('Westport','RHEL','7',80),\n       ('Schenectady','RHEL','7',80)\n```\n```\nSELECT \n    *\nFROM\n    servers;\n```\n```\n┌─datacenter──┬─distro─┬─version────┬─quantity─┐\n│ Schenectady │ Arch   │ 2020.05.01 │       10 │\n│ Schenectady │ Arch   │ 2021.09.01 │       30 │\n│ Schenectady │ Arch   │ 2022.08.05 │       50 │\n│ Schenectady │ RHEL   │ 7          │       80 │\n│ Schenectady │ RHEL   │ 9          │       60 │\n│ Westport    │ Arch   │ 2020.05.01 │        5 │\n│ Westport    │ Arch   │ 2021.09.01 │       20 │\n│ Westport    │ Arch   │ 2022.08.05 │       40 │\n│ Westport    │ RHEL   │ 7          │       80 │\n│ Westport    │ RHEL   │ 9          │       70 │\n└─────────────┴────────┴────────────┴──────────┘\n\n10 rows in set. Elapsed: 0.409 sec.\n```\n### Simple queries[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/grouping_function#simple-queries \"Direct link to Simple queries\")\nGet the count of servers in each data center by distribution:\n```\nSELECT\n    datacenter,\n    distro, \n    SUM (quantity) qty\nFROM\n    servers\nGROUP BY\n    datacenter,\n    distro;\n```\n```\n┌─datacenter──┬─distro─┬─qty─┐\n│ Schenectady │ RHEL   │ 140 │\n│ Westport    │ Arch   │  65 │\n│ Schenectady │ Arch   │  90 │\n│ Westport    │ RHEL   │ 150 │\n└─────────────┴────────┴─────┘\n\n4 rows in set. Elapsed: 0.212 sec.\n```\n```\nSELECT\n    datacenter, \n    SUM (quantity) qty\nFROM\n    servers\nGROUP BY\n    datacenter;\n```\n```\n┌─datacenter──┬─qty─┐\n│ Westport    │ 215 │\n│ Schenectady │ 230 │\n└─────────────┴─────┘\n\n2 rows in set. Elapsed: 0.277 sec.\n```\n```\nSELECT\n    distro, \n    SUM (quantity) qty\nFROM\n    servers\nGROUP BY\n    distro;\n```\n```\n\n┌─distro─┬─qty─┐\n│ Arch   │ 155 │\n│ RHEL   │ 290 │\n└────────┴─────┘\n\n2 rows in set. Elapsed: 0.352 sec.\n```\n```\nSELECT\n    SUM(quantity) qty\nFROM\n    servers;\n```\n```\n┌─qty─┐\n│ 445 │\n└─────┘\n\n1 row in set. Elapsed: 0.244 sec.\n```\n### Comparing multiple GROUP BY statements with GROUPING SETS[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/grouping_function#comparing-multiple-group-by-statements-with-grouping-sets \"Direct link to Comparing multiple GROUP BY statements with GROUPING SETS\")\nBreaking down the data without CUBE, ROLLUP, or GROUPING SETS:\n```\nSELECT\n    datacenter,\n    distro, \n    SUM (quantity) qty\nFROM\n    servers\nGROUP BY\n    datacenter,\n    distro\nUNION ALL\nSELECT\n    datacenter, \n    null,\n    SUM (quantity) qty\nFROM\n    servers\nGROUP BY\n    datacenter\nUNION ALL\nSELECT\n    null,\n    distro, \n    SUM (quantity) qty\nFROM\n    servers\nGROUP BY\n    distro\nUNION ALL\nSELECT\n    null,\n    null,\n    SUM(quantity) qty\nFROM\n    servers;\n```\n```\n┌─datacenter─┬─distro─┬─qty─┐\n│ ᴺᵁᴸᴸ       │ ᴺᵁᴸᴸ   │ 445 │\n└────────────┴────────┴─────┘\n┌─datacenter──┬─distro─┬─qty─┐\n│ Westport    │ ᴺᵁᴸᴸ   │ 215 │\n│ Schenectady │ ᴺᵁᴸᴸ   │ 230 │\n└─────────────┴────────┴─────┘\n┌─datacenter──┬─distro─┬─qty─┐\n│ Schenectady │ RHEL   │ 140 │\n│ Westport    │ Arch   │  65 │\n│ Schenectady │ Arch   │  90 │\n│ Westport    │ RHEL   │ 150 │\n└─────────────┴────────┴─────┘\n┌─datacenter─┬─distro─┬─qty─┐\n│ ᴺᵁᴸᴸ       │ Arch   │ 155 │\n│ ᴺᵁᴸᴸ       │ RHEL   │ 290 │\n└────────────┴────────┴─────┘\n\n9 rows in set. Elapsed: 0.527 sec.\n```\nGetting the same information using GROUPING SETS:\n```\nSELECT\n    datacenter,\n    distro, \n    SUM (quantity) qty\nFROM\n    servers\nGROUP BY\n    GROUPING SETS(\n        (datacenter,distro),\n        (datacenter),\n        (distro),\n        ()\n    )\n```\n```\n┌─datacenter──┬─distro─┬─qty─┐\n│ Schenectady │ RHEL   │ 140 │\n│ Westport    │ Arch   │  65 │\n│ Schenectady │ Arch   │  90 │\n│ Westport    │ RHEL   │ 150 │\n└─────────────┴────────┴─────┘\n┌─datacenter──┬─distro─┬─qty─┐\n│ Westport    │        │ 215 │\n│ Schenectady │        │ 230 │\n└─────────────┴────────┴─────┘\n┌─datacenter─┬─distro─┬─qty─┐\n│            │        │ 445 │\n└────────────┴────────┴─────┘\n┌─datacenter─┬─distro─┬─qty─┐\n│            │ Arch   │ 155 │\n│            │ RHEL   │ 290 │\n└────────────┴────────┴─────┘\n\n9 rows in set. Elapsed: 0.427 sec.\n```\n### Comparing CUBE with GROUPING SETS[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/grouping_function#comparing-cube-with-grouping-sets \"Direct link to Comparing CUBE with GROUPING SETS\")\nThe CUBE in the next query, `CUBE(datacenter,distro,version)` provides a hierarchy that may not make sense. It does not make sense to look at Version across the two distributions (as Arch and RHEL do not have the same release cycle or version naming standards). The GROUPING SETS example following this one is more appropriate as it groups `distro` and `version` in the same set.\n```\nSELECT\n   datacenter,\n   distro,\n   version,\n   SUM(quantity)\nFROM\n   servers\nGROUP BY\n   CUBE(datacenter,distro,version)\nORDER BY\n   datacenter,\n   distro;\n```\n```\n┌─datacenter──┬─distro─┬─version────┬─sum(quantity)─┐\n│             │        │ 7          │           160 │\n│             │        │ 2020.05.01 │            15 │\n│             │        │ 2021.09.01 │            50 │\n│             │        │ 2022.08.05 │            90 │\n│             │        │ 9          │           130 │\n│             │        │            │           445 │\n│             │ Arch   │ 2021.09.01 │            50 │\n│             │ Arch   │ 2022.08.05 │            90 │\n│             │ Arch   │ 2020.05.01 │            15 │\n│             │ Arch   │            │           155 │\n│             │ RHEL   │ 9          │           130 │\n│             │ RHEL   │ 7          │           160 │\n│             │ RHEL   │            │           290 │\n│ Schenectady │        │ 9          │            60 │\n│ Schenectady │        │ 2021.09.01 │            30 │\n│ Schenectady │        │ 7          │            80 │\n│ Schenectady │        │ 2022.08.05 │            50 │\n│ Schenectady │        │ 2020.05.01 │            10 │\n│ Schenectady │        │            │           230 │\n│ Schenectady │ Arch   │ 2022.08.05 │            50 │\n│ Schenectady │ Arch   │ 2021.09.01 │            30 │\n│ Schenectady │ Arch   │ 2020.05.01 │            10 │\n│ Schenectady │ Arch   │            │            90 │\n│ Schenectady │ RHEL   │ 7          │            80 │\n│ Schenectady │ RHEL   │ 9          │            60 │\n│ Schenectady │ RHEL   │            │           140 │\n│ Westport    │        │ 9          │            70 │\n│ Westport    │        │ 2020.05.01 │             5 │\n│ Westport    │        │ 2022.08.05 │            40 │\n│ Westport    │        │ 7          │            80 │\n│ Westport    │        │ 2021.09.01 │            20 │\n│ Westport    │        │            │           215 │\n│ Westport    │ Arch   │ 2020.05.01 │             5 │\n│ Westport    │ Arch   │ 2021.09.01 │            20 │\n│ Westport    │ Arch   │ 2022.08.05 │            40 │\n│ Westport    │ Arch   │            │            65 │\n│ Westport    │ RHEL   │ 9          │            70 │\n│ Westport    │ RHEL   │ 7          │            80 │\n│ Westport    │ RHEL   │            │           150 │\n└─────────────┴────────┴────────────┴───────────────┘\n\n39 rows in set. Elapsed: 0.355 sec.\n```\nNote\n\nVersion in the above example may not make sense when it is not associated with a distro, if we were tracking the kernel version it might make sense because the kernel version can be associated with either distro. Using GROUPING SETS, as in the next example, may be a better choice.\n```\nSELECT\n    datacenter,\n    distro,\n    version,\n    SUM(quantity)\nFROM servers\nGROUP BY\n    GROUPING SETS (\n        (datacenter, distro, version),\n        (datacenter, distro))\n```\n```\n┌─datacenter──┬─distro─┬─version────┬─sum(quantity)─┐\n│ Westport    │ RHEL   │ 9          │            70 │\n│ Schenectady │ Arch   │ 2022.08.05 │            50 │\n│ Schenectady │ Arch   │ 2021.09.01 │            30 │\n│ Schenectady │ RHEL   │ 7          │            80 │\n│ Westport    │ Arch   │ 2020.05.01 │             5 │\n│ Westport    │ RHEL   │ 7          │            80 │\n│ Westport    │ Arch   │ 2021.09.01 │            20 │\n│ Westport    │ Arch   │ 2022.08.05 │            40 │\n│ Schenectady │ RHEL   │ 9          │            60 │\n│ Schenectady │ Arch   │ 2020.05.01 │            10 │\n└─────────────┴────────┴────────────┴───────────────┘\n┌─datacenter──┬─distro─┬─version─┬─sum(quantity)─┐\n│ Schenectady │ RHEL   │         │           140 │\n│ Westport    │ Arch   │         │            65 │\n│ Schenectady │ Arch   │         │            90 │\n│ Westport    │ RHEL   │         │           150 │\n└─────────────┴────────┴─────────┴───────────────┘\n\n14 rows in set. Elapsed: 1.036 sec.\n```\n\n[Previous Parametric](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/parametric-functions)\n\n[Next anyIf](https:\/\/clickhouse.com\/docs\/examples\/aggregate-function-combinators\/anyIf)\n\n- [GROUPING](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/grouping_function#grouping)\n- [GROUPING SETS](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/grouping_function#grouping-sets)\n  - [Load sample data](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/grouping_function#load-sample-data)\n  - [Simple queries](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/grouping_function#simple-queries)\n  - [Comparing multiple GROUP BY statements with GROUPING SETS](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/grouping_function#comparing-multiple-group-by-statements-with-grouping-sets)\n  - [Comparing CUBE with GROUPING SETS](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/grouping_function#comparing-cube-with-grouping-sets)\n\nWas this page helpful?\n\n###### Try ClickHouse Cloud for FREE\nSeparation of storage and compute, automatic scaling, built-in SQL console, and lots more. \\$300 in free credits when signing up.\n\n[Try it for Free](https:\/\/console.clickhouse.cloud\/signUp?loc=doc-card-banner&glxid=e936910f-4374-41f2-9fe9-b48f22a26d3a&pagePath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fgrouping_function&origPath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fgrouping_function&utm_ga=GA1.1.1997658363.1775979289)\n\n© 2016–2026 ClickHouse, Inc.\n\n[Trademark](https:\/\/clickhouse.com\/legal\/trademark-policy)·[Privacy](https:\/\/clickhouse.com\/legal\/privacy-policy)·[Security](https:\/\/trust.clickhouse.com\/)·[Terms of Service](https:\/\/clickhouse.com\/legal\/agreements\/terms-of-service)\n\n![](https:\/\/static.scarf.sh\/a.png?x-pxid=e6377503-591b-4886-9398-e69c7fee0b91)\n\n© 2016–2026 ClickHouse, Inc.\n\n[Trademark](https:\/\/clickhouse.com\/legal\/trademark-policy)·[Privacy](https:\/\/clickhouse.com\/legal\/privacy-policy)·[Security](https:\/\/trust.clickhouse.com\/)·[Terms of Service](https:\/\/clickhouse.com\/legal\/agreements\/terms-of-service)\n\n![](https:\/\/static.scarf.sh\/a.png?x-pxid=e6377503-591b-4886-9398-e69c7fee0b91)\n\n[![ClickHouse](https:\/\/clickhouse.com\/docs\/img\/ch_logo_docs.svg)](https:\/\/clickhouse.com\/)\n\nEN\n\n- Get started▼\n- Cloud▼\n- Manage data▼\n- Server admin▼\n- Reference▼\n- Integrations▼\n- ClickStack▼\n- chDB▼\n- About▼\n\n[![ClickHouse](https:\/\/clickhouse.com\/docs\/img\/ch_logo_docs.svg)](https:\/\/clickhouse.com\/)\n\nEN\n\nmain-menu\n\n- Introduction▼\n  - [Syntax](https:\/\/clickhouse.com\/docs\/sql-reference\/syntax)\n  - [Input and Output Formats](https:\/\/clickhouse.com\/docs\/sql-reference\/formats)\n  - Data types▼\n  - Statements▼\n  - Operators▼\n- Engines▼\n- Functions▼\n  - Regular functions▼\n  - Aggregate functions▼\n    - Aggregate Functions▼\n    - [Combinators](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators)\n    - [Parametric](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/parametric-functions)\n    - [GROUPING](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/grouping_function)\n    - Combinator examples▼\n  - Table functions▼\n  - Window functions▼\n- Formats▼\n- [Data Lakes](https:\/\/clickhouse.com\/docs\/sql-reference\/datalakes)","attrs_readable_markdown":"## GROUPING[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/grouping_function#grouping \"Direct link to GROUPING\")\n[ROLLUP](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/group-by#rollup-modifier) and [CUBE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/group-by#cube-modifier) are modifiers to GROUP BY. Both of these calculate subtotals. ROLLUP takes an ordered list of columns, for example `(day, month, year)`, and calculates subtotals at each level of the aggregation and then a grand total. CUBE calculates subtotals across all possible combinations of the columns specified. GROUPING identifies which rows returned by ROLLUP or CUBE are superaggregates, and which are rows that would be returned by an unmodified GROUP BY.\n\nThe GROUPING function takes multiple columns as an argument, and returns a bitmask.\n\n- `1` indicates that a row returned by a `ROLLUP` or `CUBE` modifier to `GROUP BY` is a subtotal\n- `0` indicates that a row returned by a `ROLLUP` or `CUBE` is a row that is not a subtotal\n## GROUPING SETS[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/grouping_function#grouping-sets \"Direct link to GROUPING SETS\")\nBy default, the CUBE modifier calculates subtotals for all possible combinations of the columns passed to CUBE. GROUPING SETS allows you to specify the specific combinations to calculate.\n\nAnalyzing hierarchical data is a good use case for ROLLUP, CUBE, and GROUPING SETS modifiers. The sample here is a table containing data about what Linux distribution, and the version of that distribution is installed across two datacenters. It may be valuable to look at the data by distribution, version, and location.\n\n### Load sample data[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/grouping_function#load-sample-data \"Direct link to Load sample data\")\n```\nCREATE TABLE servers ( datacenter VARCHAR(255),\n                         distro VARCHAR(255) NOT NULL,\n                         version VARCHAR(50) NOT NULL,\n                         quantity INT\n                       )\n                        ORDER BY (datacenter, distro, version)\n```\n```\nINSERT INTO servers(datacenter, distro, version, quantity)\nVALUES ('Schenectady', 'Arch','2022.08.05',50),\n       ('Westport', 'Arch','2022.08.05',40),\n       ('Schenectady','Arch','2021.09.01',30),\n       ('Westport', 'Arch','2021.09.01',20),\n       ('Schenectady','Arch','2020.05.01',10),\n       ('Westport', 'Arch','2020.05.01',5),\n       ('Schenectady','RHEL','9',60),\n       ('Westport','RHEL','9',70),\n       ('Westport','RHEL','7',80),\n       ('Schenectady','RHEL','7',80)\n```\n```\nSELECT \n    *\nFROM\n    servers;\n```\n```\n┌─datacenter──┬─distro─┬─version────┬─quantity─┐\n│ Schenectady │ Arch   │ 2020.05.01 │       10 │\n│ Schenectady │ Arch   │ 2021.09.01 │       30 │\n│ Schenectady │ Arch   │ 2022.08.05 │       50 │\n│ Schenectady │ RHEL   │ 7          │       80 │\n│ Schenectady │ RHEL   │ 9          │       60 │\n│ Westport    │ Arch   │ 2020.05.01 │        5 │\n│ Westport    │ Arch   │ 2021.09.01 │       20 │\n│ Westport    │ Arch   │ 2022.08.05 │       40 │\n│ Westport    │ RHEL   │ 7          │       80 │\n│ Westport    │ RHEL   │ 9          │       70 │\n└─────────────┴────────┴────────────┴──────────┘\n\n10 rows in set. Elapsed: 0.409 sec.\n```\n### Simple queries[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/grouping_function#simple-queries \"Direct link to Simple queries\")\nGet the count of servers in each data center by distribution:\n```\nSELECT\n    datacenter,\n    distro, \n    SUM (quantity) qty\nFROM\n    servers\nGROUP BY\n    datacenter,\n    distro;\n```\n```\n┌─datacenter──┬─distro─┬─qty─┐\n│ Schenectady │ RHEL   │ 140 │\n│ Westport    │ Arch   │  65 │\n│ Schenectady │ Arch   │  90 │\n│ Westport    │ RHEL   │ 150 │\n└─────────────┴────────┴─────┘\n\n4 rows in set. Elapsed: 0.212 sec.\n```\n```\nSELECT\n    datacenter, \n    SUM (quantity) qty\nFROM\n    servers\nGROUP BY\n    datacenter;\n```\n```\n┌─datacenter──┬─qty─┐\n│ Westport    │ 215 │\n│ Schenectady │ 230 │\n└─────────────┴─────┘\n\n2 rows in set. Elapsed: 0.277 sec.\n```\n```\nSELECT\n    distro, \n    SUM (quantity) qty\nFROM\n    servers\nGROUP BY\n    distro;\n```\n```\n\n┌─distro─┬─qty─┐\n│ Arch   │ 155 │\n│ RHEL   │ 290 │\n└────────┴─────┘\n\n2 rows in set. Elapsed: 0.352 sec.\n```\n```\nSELECT\n    SUM(quantity) qty\nFROM\n    servers;\n```\n```\n┌─qty─┐\n│ 445 │\n└─────┘\n\n1 row in set. Elapsed: 0.244 sec.\n```\n### Comparing multiple GROUP BY statements with GROUPING SETS[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/grouping_function#comparing-multiple-group-by-statements-with-grouping-sets \"Direct link to Comparing multiple GROUP BY statements with GROUPING SETS\")\nBreaking down the data without CUBE, ROLLUP, or GROUPING SETS:\n```\nSELECT\n    datacenter,\n    distro, \n    SUM (quantity) qty\nFROM\n    servers\nGROUP BY\n    datacenter,\n    distro\nUNION ALL\nSELECT\n    datacenter, \n    null,\n    SUM (quantity) qty\nFROM\n    servers\nGROUP BY\n    datacenter\nUNION ALL\nSELECT\n    null,\n    distro, \n    SUM (quantity) qty\nFROM\n    servers\nGROUP BY\n    distro\nUNION ALL\nSELECT\n    null,\n    null,\n    SUM(quantity) qty\nFROM\n    servers;\n```\n```\n┌─datacenter─┬─distro─┬─qty─┐\n│ ᴺᵁᴸᴸ       │ ᴺᵁᴸᴸ   │ 445 │\n└────────────┴────────┴─────┘\n┌─datacenter──┬─distro─┬─qty─┐\n│ Westport    │ ᴺᵁᴸᴸ   │ 215 │\n│ Schenectady │ ᴺᵁᴸᴸ   │ 230 │\n└─────────────┴────────┴─────┘\n┌─datacenter──┬─distro─┬─qty─┐\n│ Schenectady │ RHEL   │ 140 │\n│ Westport    │ Arch   │  65 │\n│ Schenectady │ Arch   │  90 │\n│ Westport    │ RHEL   │ 150 │\n└─────────────┴────────┴─────┘\n┌─datacenter─┬─distro─┬─qty─┐\n│ ᴺᵁᴸᴸ       │ Arch   │ 155 │\n│ ᴺᵁᴸᴸ       │ RHEL   │ 290 │\n└────────────┴────────┴─────┘\n\n9 rows in set. Elapsed: 0.527 sec.\n```\nGetting the same information using GROUPING SETS:\n```\nSELECT\n    datacenter,\n    distro, \n    SUM (quantity) qty\nFROM\n    servers\nGROUP BY\n    GROUPING SETS(\n        (datacenter,distro),\n        (datacenter),\n        (distro),\n        ()\n    )\n```\n```\n┌─datacenter──┬─distro─┬─qty─┐\n│ Schenectady │ RHEL   │ 140 │\n│ Westport    │ Arch   │  65 │\n│ Schenectady │ Arch   │  90 │\n│ Westport    │ RHEL   │ 150 │\n└─────────────┴────────┴─────┘\n┌─datacenter──┬─distro─┬─qty─┐\n│ Westport    │        │ 215 │\n│ Schenectady │        │ 230 │\n└─────────────┴────────┴─────┘\n┌─datacenter─┬─distro─┬─qty─┐\n│            │        │ 445 │\n└────────────┴────────┴─────┘\n┌─datacenter─┬─distro─┬─qty─┐\n│            │ Arch   │ 155 │\n│            │ RHEL   │ 290 │\n└────────────┴────────┴─────┘\n\n9 rows in set. Elapsed: 0.427 sec.\n```\n### Comparing CUBE with GROUPING SETS[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/grouping_function#comparing-cube-with-grouping-sets \"Direct link to Comparing CUBE with GROUPING SETS\")\nThe CUBE in the next query, `CUBE(datacenter,distro,version)` provides a hierarchy that may not make sense. It does not make sense to look at Version across the two distributions (as Arch and RHEL do not have the same release cycle or version naming standards). The GROUPING SETS example following this one is more appropriate as it groups `distro` and `version` in the same set.\n```\nSELECT\n   datacenter,\n   distro,\n   version,\n   SUM(quantity)\nFROM\n   servers\nGROUP BY\n   CUBE(datacenter,distro,version)\nORDER BY\n   datacenter,\n   distro;\n```\n```\n┌─datacenter──┬─distro─┬─version────┬─sum(quantity)─┐\n│             │        │ 7          │           160 │\n│             │        │ 2020.05.01 │            15 │\n│             │        │ 2021.09.01 │            50 │\n│             │        │ 2022.08.05 │            90 │\n│             │        │ 9          │           130 │\n│             │        │            │           445 │\n│             │ Arch   │ 2021.09.01 │            50 │\n│             │ Arch   │ 2022.08.05 │            90 │\n│             │ Arch   │ 2020.05.01 │            15 │\n│             │ Arch   │            │           155 │\n│             │ RHEL   │ 9          │           130 │\n│             │ RHEL   │ 7          │           160 │\n│             │ RHEL   │            │           290 │\n│ Schenectady │        │ 9          │            60 │\n│ Schenectady │        │ 2021.09.01 │            30 │\n│ Schenectady │        │ 7          │            80 │\n│ Schenectady │        │ 2022.08.05 │            50 │\n│ Schenectady │        │ 2020.05.01 │            10 │\n│ Schenectady │        │            │           230 │\n│ Schenectady │ Arch   │ 2022.08.05 │            50 │\n│ Schenectady │ Arch   │ 2021.09.01 │            30 │\n│ Schenectady │ Arch   │ 2020.05.01 │            10 │\n│ Schenectady │ Arch   │            │            90 │\n│ Schenectady │ RHEL   │ 7          │            80 │\n│ Schenectady │ RHEL   │ 9          │            60 │\n│ Schenectady │ RHEL   │            │           140 │\n│ Westport    │        │ 9          │            70 │\n│ Westport    │        │ 2020.05.01 │             5 │\n│ Westport    │        │ 2022.08.05 │            40 │\n│ Westport    │        │ 7          │            80 │\n│ Westport    │        │ 2021.09.01 │            20 │\n│ Westport    │        │            │           215 │\n│ Westport    │ Arch   │ 2020.05.01 │             5 │\n│ Westport    │ Arch   │ 2021.09.01 │            20 │\n│ Westport    │ Arch   │ 2022.08.05 │            40 │\n│ Westport    │ Arch   │            │            65 │\n│ Westport    │ RHEL   │ 9          │            70 │\n│ Westport    │ RHEL   │ 7          │            80 │\n│ Westport    │ RHEL   │            │           150 │\n└─────────────┴────────┴────────────┴───────────────┘\n\n39 rows in set. Elapsed: 0.355 sec.\n```\nNote\n\nVersion in the above example may not make sense when it is not associated with a distro, if we were tracking the kernel version it might make sense because the kernel version can be associated with either distro. Using GROUPING SETS, as in the next example, may be a better choice.\n```\nSELECT\n    datacenter,\n    distro,\n    version,\n    SUM(quantity)\nFROM servers\nGROUP BY\n    GROUPING SETS (\n        (datacenter, distro, version),\n        (datacenter, distro))\n```\n```\n┌─datacenter──┬─distro─┬─version────┬─sum(quantity)─┐\n│ Westport    │ RHEL   │ 9          │            70 │\n│ Schenectady │ Arch   │ 2022.08.05 │            50 │\n│ Schenectady │ Arch   │ 2021.09.01 │            30 │\n│ Schenectady │ RHEL   │ 7          │            80 │\n│ Westport    │ Arch   │ 2020.05.01 │             5 │\n│ Westport    │ RHEL   │ 7          │            80 │\n│ Westport    │ Arch   │ 2021.09.01 │            20 │\n│ Westport    │ Arch   │ 2022.08.05 │            40 │\n│ Schenectady │ RHEL   │ 9          │            60 │\n│ Schenectady │ Arch   │ 2020.05.01 │            10 │\n└─────────────┴────────┴────────────┴───────────────┘\n┌─datacenter──┬─distro─┬─version─┬─sum(quantity)─┐\n│ Schenectady │ RHEL   │         │           140 │\n│ Westport    │ Arch   │         │            65 │\n│ Schenectady │ Arch   │         │            90 │\n│ Westport    │ RHEL   │         │           150 │\n└─────────────┴────────┴─────────┴───────────────┘\n\n14 rows in set. Elapsed: 1.036 sec.\n```","meta_canonical":null}

3. Robots.txt Check

Query:

Response:

4. Spam/Ban Check

Query:

Response:

5. Seen Status Check

ℹ️ Skipped - page is already crawled

📄

INDEXABLE

✅

CRAWLED

2 days ago

🤖

ROBOTS ALLOWED

Page Info Filters

Filter	Status	Condition	Details
HTTP status	PASS	`download_http_code = 200`	HTTP 200
Age cutoff	PASS	`download_stamp > now() - 6 MONTH`	0.1 months ago
History drop	PASS	`isNull(history_drop_reason)`	No drop reason
Spam/ban	PASS	`fh_dont_index != 1 AND ml_spam_score = 0`	ml_spam_score=0
Canonical	PASS	`meta_canonical IS NULL OR = '' OR = src_unparsed`	Not set

Page Details

Property	Value
URL	https://clickhouse.com/docs/sql-reference/aggregate-functions/grouping_function
Last Crawled	2026-04-12 07:34:47 (2 days ago)
First Indexed	2025-02-21 02:43:43 (1 year ago)
HTTP Status Code	200
Meta Title	GROUPING \| ClickHouse Docs
Meta Description	Documentation for the GROUPING aggregate function.
Meta Canonical	null
Boilerpipe Text	GROUPING ROLLUP and CUBE are modifiers to GROUP BY. Both of these calculate subtotals. ROLLUP takes an ordered list of columns, for example (day, month, year) , and calculates subtotals at each level of the aggregation and then a grand total. CUBE calculates subtotals across all possible combinations of the columns specified. GROUPING identifies which rows returned by ROLLUP or CUBE are superaggregates, and which are rows that would be returned by an unmodified GROUP BY. The GROUPING function takes multiple columns as an argument, and returns a bitmask. 1 indicates that a row returned by a ROLLUP or CUBE modifier to GROUP BY is a subtotal 0 indicates that a row returned by a ROLLUP or CUBE is a row that is not a subtotal GROUPING SETS By default, the CUBE modifier calculates subtotals for all possible combinations of the columns passed to CUBE. GROUPING SETS allows you to specify the specific combinations to calculate. Analyzing hierarchical data is a good use case for ROLLUP, CUBE, and GROUPING SETS modifiers. The sample here is a table containing data about what Linux distribution, and the version of that distribution is installed across two datacenters. It may be valuable to look at the data by distribution, version, and location. Load sample data CREATE TABLE servers ( datacenter VARCHAR ( 255 ) , distro VARCHAR ( 255 ) NOT NULL , version VARCHAR ( 50 ) NOT NULL , quantity INT ) ORDER BY ( datacenter , distro , version ) INSERT INTO servers ( datacenter , distro , version , quantity ) VALUES ( 'Schenectady' , 'Arch' , '2022.08.05' , 50 ) , ( 'Westport' , 'Arch' , '2022.08.05' , 40 ) , ( 'Schenectady' , 'Arch' , '2021.09.01' , 30 ) , ( 'Westport' , 'Arch' , '2021.09.01' , 20 ) , ( 'Schenectady' , 'Arch' , '2020.05.01' , 10 ) , ( 'Westport' , 'Arch' , '2020.05.01' , 5 ) , ( 'Schenectady' , 'RHEL' , '9' , 60 ) , ( 'Westport' , 'RHEL' , '9' , 70 ) , ( 'Westport' , 'RHEL' , '7' , 80 ) , ( 'Schenectady' , 'RHEL' , '7' , 80 ) SELECT * FROM servers ; ┌─datacenter──┬─distro─┬─version────┬─quantity─┐ │ Schenectady │ Arch │ 2020.05.01 │ 10 │ │ Schenectady │ Arch │ 2021.09.01 │ 30 │ │ Schenectady │ Arch │ 2022.08.05 │ 50 │ │ Schenectady │ RHEL │ 7 │ 80 │ │ Schenectady │ RHEL │ 9 │ 60 │ │ Westport │ Arch │ 2020.05.01 │ 5 │ │ Westport │ Arch │ 2021.09.01 │ 20 │ │ Westport │ Arch │ 2022.08.05 │ 40 │ │ Westport │ RHEL │ 7 │ 80 │ │ Westport │ RHEL │ 9 │ 70 │ └─────────────┴────────┴────────────┴──────────┘ 10 rows in set. Elapsed: 0.409 sec. Simple queries Get the count of servers in each data center by distribution: SELECT datacenter , distro , SUM ( quantity ) qty FROM servers GROUP BY datacenter , distro ; ┌─datacenter──┬─distro─┬─qty─┐ │ Schenectady │ RHEL │ 140 │ │ Westport │ Arch │ 65 │ │ Schenectady │ Arch │ 90 │ │ Westport │ RHEL │ 150 │ └─────────────┴────────┴─────┘ 4 rows in set. Elapsed: 0.212 sec. SELECT datacenter , SUM ( quantity ) qty FROM servers GROUP BY datacenter ; ┌─datacenter──┬─qty─┐ │ Westport │ 215 │ │ Schenectady │ 230 │ └─────────────┴─────┘ 2 rows in set. Elapsed: 0.277 sec. SELECT distro , SUM ( quantity ) qty FROM servers GROUP BY distro ; ┌─distro─┬─qty─┐ │ Arch │ 155 │ │ RHEL │ 290 │ └────────┴─────┘ 2 rows in set. Elapsed: 0.352 sec. SELECT SUM ( quantity ) qty FROM servers ; ┌─qty─┐ │ 445 │ └─────┘ 1 row in set. Elapsed: 0.244 sec. Comparing multiple GROUP BY statements with GROUPING SETS Breaking down the data without CUBE, ROLLUP, or GROUPING SETS: SELECT datacenter , distro , SUM ( quantity ) qty FROM servers GROUP BY datacenter , distro UNION ALL SELECT datacenter , null , SUM ( quantity ) qty FROM servers GROUP BY datacenter UNION ALL SELECT null , distro , SUM ( quantity ) qty FROM servers GROUP BY distro UNION ALL SELECT null , null , SUM ( quantity ) qty FROM servers ; ┌─datacenter─┬─distro─┬─qty─┐ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 445 │ └────────────┴────────┴─────┘ ┌─datacenter──┬─distro─┬─qty─┐ │ Westport │ ᴺᵁᴸᴸ │ 215 │ │ Schenectady │ ᴺᵁᴸᴸ │ 230 │ └─────────────┴────────┴─────┘ ┌─datacenter──┬─distro─┬─qty─┐ │ Schenectady │ RHEL │ 140 │ │ Westport │ Arch │ 65 │ │ Schenectady │ Arch │ 90 │ │ Westport │ RHEL │ 150 │ └─────────────┴────────┴─────┘ ┌─datacenter─┬─distro─┬─qty─┐ │ ᴺᵁᴸᴸ │ Arch │ 155 │ │ ᴺᵁᴸᴸ │ RHEL │ 290 │ └────────────┴────────┴─────┘ 9 rows in set. Elapsed: 0.527 sec. Getting the same information using GROUPING SETS: SELECT datacenter , distro , SUM ( quantity ) qty FROM servers GROUP BY GROUPING SETS ( ( datacenter , distro ) , ( datacenter ) , ( distro ) , ( ) ) ┌─datacenter──┬─distro─┬─qty─┐ │ Schenectady │ RHEL │ 140 │ │ Westport │ Arch │ 65 │ │ Schenectady │ Arch │ 90 │ │ Westport │ RHEL │ 150 │ └─────────────┴────────┴─────┘ ┌─datacenter──┬─distro─┬─qty─┐ │ Westport │ │ 215 │ │ Schenectady │ │ 230 │ └─────────────┴────────┴─────┘ ┌─datacenter─┬─distro─┬─qty─┐ │ │ │ 445 │ └────────────┴────────┴─────┘ ┌─datacenter─┬─distro─┬─qty─┐ │ │ Arch │ 155 │ │ │ RHEL │ 290 │ └────────────┴────────┴─────┘ 9 rows in set. Elapsed: 0.427 sec. Comparing CUBE with GROUPING SETS The CUBE in the next query, CUBE(datacenter,distro,version) provides a hierarchy that may not make sense. It does not make sense to look at Version across the two distributions (as Arch and RHEL do not have the same release cycle or version naming standards). The GROUPING SETS example following this one is more appropriate as it groups distro and version in the same set. SELECT datacenter , distro , version , SUM ( quantity ) FROM servers GROUP BY CUBE ( datacenter , distro , version ) ORDER BY datacenter , distro ; ┌─datacenter──┬─distro─┬─version────┬─sum(quantity)─┐ │ │ │ 7 │ 160 │ │ │ │ 2020.05.01 │ 15 │ │ │ │ 2021.09.01 │ 50 │ │ │ │ 2022.08.05 │ 90 │ │ │ │ 9 │ 130 │ │ │ │ │ 445 │ │ │ Arch │ 2021.09.01 │ 50 │ │ │ Arch │ 2022.08.05 │ 90 │ │ │ Arch │ 2020.05.01 │ 15 │ │ │ Arch │ │ 155 │ │ │ RHEL │ 9 │ 130 │ │ │ RHEL │ 7 │ 160 │ │ │ RHEL │ │ 290 │ │ Schenectady │ │ 9 │ 60 │ │ Schenectady │ │ 2021.09.01 │ 30 │ │ Schenectady │ │ 7 │ 80 │ │ Schenectady │ │ 2022.08.05 │ 50 │ │ Schenectady │ │ 2020.05.01 │ 10 │ │ Schenectady │ │ │ 230 │ │ Schenectady │ Arch │ 2022.08.05 │ 50 │ │ Schenectady │ Arch │ 2021.09.01 │ 30 │ │ Schenectady │ Arch │ 2020.05.01 │ 10 │ │ Schenectady │ Arch │ │ 90 │ │ Schenectady │ RHEL │ 7 │ 80 │ │ Schenectady │ RHEL │ 9 │ 60 │ │ Schenectady │ RHEL │ │ 140 │ │ Westport │ │ 9 │ 70 │ │ Westport │ │ 2020.05.01 │ 5 │ │ Westport │ │ 2022.08.05 │ 40 │ │ Westport │ │ 7 │ 80 │ │ Westport │ │ 2021.09.01 │ 20 │ │ Westport │ │ │ 215 │ │ Westport │ Arch │ 2020.05.01 │ 5 │ │ Westport │ Arch │ 2021.09.01 │ 20 │ │ Westport │ Arch │ 2022.08.05 │ 40 │ │ Westport │ Arch │ │ 65 │ │ Westport │ RHEL │ 9 │ 70 │ │ Westport │ RHEL │ 7 │ 80 │ │ Westport │ RHEL │ │ 150 │ └─────────────┴────────┴────────────┴───────────────┘ 39 rows in set. Elapsed: 0.355 sec. Note Version in the above example may not make sense when it is not associated with a distro, if we were tracking the kernel version it might make sense because the kernel version can be associated with either distro. Using GROUPING SETS, as in the next example, may be a better choice. SELECT datacenter , distro , version , SUM ( quantity ) FROM servers GROUP BY GROUPING SETS ( ( datacenter , distro , version ) , ( datacenter , distro ) ) ┌─datacenter──┬─distro─┬─version────┬─sum(quantity)─┐ │ Westport │ RHEL │ 9 │ 70 │ │ Schenectady │ Arch │ 2022.08.05 │ 50 │ │ Schenectady │ Arch │ 2021.09.01 │ 30 │ │ Schenectady │ RHEL │ 7 │ 80 │ │ Westport │ Arch │ 2020.05.01 │ 5 │ │ Westport │ RHEL │ 7 │ 80 │ │ Westport │ Arch │ 2021.09.01 │ 20 │ │ Westport │ Arch │ 2022.08.05 │ 40 │ │ Schenectady │ RHEL │ 9 │ 60 │ │ Schenectady │ Arch │ 2020.05.01 │ 10 │ └─────────────┴────────┴────────────┴───────────────┘ ┌─datacenter──┬─distro─┬─version─┬─sum(quantity)─┐ │ Schenectady │ RHEL │ │ 140 │ │ Westport │ Arch │ │ 65 │ │ Schenectady │ Arch │ │ 90 │ │ Westport │ RHEL │ │ 150 │ └─────────────┴────────┴─────────┴───────────────┘ 14 rows in set. Elapsed: 1.036 sec.
Markdown	[Skip to main content](https://clickhouse.com/docs/sql-reference/aggregate-functions/grouping_function#__docusaurus_skipToContent_fallback) [![ClickHouse](https://clickhouse.com/docs/img/ch_logo_docs.svg)](https://clickhouse.com/) - [Products](https://clickhouse.com/docs/sql-reference/aggregate-functions/grouping_function) - [ClickHouse Cloud Best way to use ClickHouse. Available on AWS, GCP, and Azure.](https://clickhouse.com/cloud) - [BYOC (Bring Your Own Cloud) The fully managed ClickHouse Cloud service, Can be deployed in your AWS account.](https://clickhouse.com/cloud/bring-your-own-cloud) - [ClickHouse Set up a database with open-source ClickHouse. ClickHouse](https://clickhouse.com/clickhouse) - [Discover more than 100 integrations.](https://clickhouse.com/integrations) [Discover more than 100 integrations.](https://clickhouse.com/integrations) - [Use cases](https://clickhouse.com/docs/sql-reference/aggregate-functions/grouping_function) - [Real-time analytics](https://clickhouse.com/use-cases/real-time-analytics) - [Machine Learning & Generative AI](https://clickhouse.com/use-cases/machine-learning-and-data-science) - [Business Intelligence](https://clickhouse.com/use-cases/data-warehousing) - [Logs, Events, Traces](https://clickhouse.com/use-cases/observability) - [All use cases](https://clickhouse.com/use-cases) [All use cases](https://clickhouse.com/use-cases) - [Documentation](https://clickhouse.com/docs) - [Resources](https://clickhouse.com/docs/sql-reference/aggregate-functions/grouping_function) - [User stories](https://clickhouse.com/user-stories) - [Blog](https://clickhouse.com/blog) - [Events](https://clickhouse.com/company/events) - [Learning and certification](https://clickhouse.com/learn) - [Comparison](https://clickhouse.com/docs/sql-reference/aggregate-functions/grouping_function) - [BigQuery](https://clickhouse.com/comparison/bigquery) - [PostgreSQL](https://clickhouse.com/comparison/postgresql) - [Redshift](https://clickhouse.com/comparison/redshift) - [Rockset](https://clickhouse.com/comparison/rockset) - [Snowflake](https://clickhouse.com/comparison/snowflake) - [Video](https://clickhouse.com/videos) - [Demo](https://clickhouse.com/demos) - [Pricing](https://clickhouse.com/pricing) - [Contact](https://clickhouse.com/company/contact?loc=nav) [46\.8k](https://github.com/ClickHouse/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav) [Search`Ctrl``K`](https://clickhouse.com/docs/search) [Sign in](https://console.clickhouse.cloud/signIn?loc=docs-nav-signIn-cta&glxid=e936910f-4374-41f2-9fe9-b48f22a26d3a&pagePath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fgrouping_function&origPath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fgrouping_function&utm_ga=GA1.1.1997658363.1775979289) [Get started](https://console.clickhouse.cloud/signUp?loc=docs-nav-signUp-cta&glxid=e936910f-4374-41f2-9fe9-b48f22a26d3a&pagePath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fgrouping_function&origPath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fgrouping_function&utm_ga=GA1.1.1997658363.1775979289) [Get started](https://clickhouse.com/docs/introduction-clickhouse) [Cloud](https://clickhouse.com/docs/cloud/overview) [Manage data](https://clickhouse.com/docs/updating-data) [Server admin](https://clickhouse.com/docs/guides/manage-and-deploy-index) [Reference](https://clickhouse.com/docs/sql-reference) [Integrations](https://clickhouse.com/docs/integrations) [ClickStack](https://clickhouse.com/docs/use-cases/observability/clickstack/overview) [chDB](https://clickhouse.com/docs/chdb) [About](https://clickhouse.com/docs/about) [Knowledge Base](https://clickhouse.com/docs/knowledgebase) [English](https://clickhouse.com/docs/sql-reference/aggregate-functions/grouping_function) - [English](https://clickhouse.com/docs/sql-reference/aggregate-functions/grouping_function) - [日本語](https://clickhouse.com/docs/jp/sql-reference/aggregate-functions/grouping_function) - [中文](https://clickhouse.com/docs/zh/sql-reference/aggregate-functions/grouping_function) - [Русский](https://clickhouse.com/docs/ru/sql-reference/aggregate-functions/grouping_function) - [한국어](https://clickhouse.com/docs/ko/sql-reference/aggregate-functions/grouping_function) [Skip to main content](https://clickhouse.com/docs/sql-reference/aggregate-functions/grouping_function#__docusaurus_skipToContent_fallback) [![ClickHouse](https://clickhouse.com/docs/img/ch_logo_docs.svg)](https://clickhouse.com/) - [Products](https://clickhouse.com/docs/sql-reference/aggregate-functions/grouping_function) - [ClickHouse Cloud Best way to use ClickHouse. Available on AWS, GCP, and Azure.](https://clickhouse.com/cloud) - [BYOC (Bring Your Own Cloud) The fully managed ClickHouse Cloud service, Can be deployed in your AWS account.](https://clickhouse.com/cloud/bring-your-own-cloud) - [ClickHouse Set up a database with open-source ClickHouse. ClickHouse](https://clickhouse.com/clickhouse) - [Discover more than 100 integrations.](https://clickhouse.com/integrations) [Discover more than 100 integrations.](https://clickhouse.com/integrations) - [Use cases](https://clickhouse.com/docs/sql-reference/aggregate-functions/grouping_function) - [Real-time analytics](https://clickhouse.com/use-cases/real-time-analytics) - [Machine Learning & Generative AI](https://clickhouse.com/use-cases/machine-learning-and-data-science) - [Business Intelligence](https://clickhouse.com/use-cases/data-warehousing) - [Logs, Events, Traces](https://clickhouse.com/use-cases/observability) - [All use cases](https://clickhouse.com/use-cases) [All use cases](https://clickhouse.com/use-cases) - [Documentation](https://clickhouse.com/docs) - [Resources](https://clickhouse.com/docs/sql-reference/aggregate-functions/grouping_function) - [User stories](https://clickhouse.com/user-stories) - [Blog](https://clickhouse.com/blog) - [Events](https://clickhouse.com/company/events) - [Learning and certification](https://clickhouse.com/learn) - [Comparison](https://clickhouse.com/docs/sql-reference/aggregate-functions/grouping_function) - [BigQuery](https://clickhouse.com/comparison/bigquery) - [PostgreSQL](https://clickhouse.com/comparison/postgresql) - [Redshift](https://clickhouse.com/comparison/redshift) - [Rockset](https://clickhouse.com/comparison/rockset) - [Snowflake](https://clickhouse.com/comparison/snowflake) - [Video](https://clickhouse.com/videos) - [Demo](https://clickhouse.com/demos) - [Pricing](https://clickhouse.com/pricing) - [Contact](https://clickhouse.com/company/contact?loc=nav) [46\.8k](https://github.com/ClickHouse/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav) [Search`Ctrl``K`](https://clickhouse.com/docs/search) [Sign in](https://console.clickhouse.cloud/signIn?loc=docs-nav-signIn-cta&glxid=e936910f-4374-41f2-9fe9-b48f22a26d3a&pagePath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fgrouping_function&origPath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fgrouping_function&utm_ga=GA1.1.1997658363.1775979289) [Get started](https://console.clickhouse.cloud/signUp?loc=docs-nav-signUp-cta&glxid=e936910f-4374-41f2-9fe9-b48f22a26d3a&pagePath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fgrouping_function&origPath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fgrouping_function&utm_ga=GA1.1.1997658363.1775979289) [Get started](https://clickhouse.com/docs/introduction-clickhouse) [Cloud](https://clickhouse.com/docs/cloud/overview) [Manage data](https://clickhouse.com/docs/updating-data) [Server admin](https://clickhouse.com/docs/guides/manage-and-deploy-index) [Reference](https://clickhouse.com/docs/sql-reference) [Integrations](https://clickhouse.com/docs/integrations) [ClickStack](https://clickhouse.com/docs/use-cases/observability/clickstack/overview) [chDB](https://clickhouse.com/docs/chdb) [About](https://clickhouse.com/docs/about) [Knowledge Base](https://clickhouse.com/docs/knowledgebase) [English](https://clickhouse.com/docs/sql-reference/aggregate-functions/grouping_function) - [English](https://clickhouse.com/docs/sql-reference/aggregate-functions/grouping_function) - [日本語](https://clickhouse.com/docs/jp/sql-reference/aggregate-functions/grouping_function) - [中文](https://clickhouse.com/docs/zh/sql-reference/aggregate-functions/grouping_function) - [Русский](https://clickhouse.com/docs/ru/sql-reference/aggregate-functions/grouping_function) - [한국어](https://clickhouse.com/docs/ko/sql-reference/aggregate-functions/grouping_function) [Search`Ctrl``K`](https://clickhouse.com/docs/search) - [Introduction](https://clickhouse.com/docs/sql-reference) - [Syntax](https://clickhouse.com/docs/sql-reference/syntax) - [Input and Output Formats](https://clickhouse.com/docs/sql-reference/formats) - [Data types](https://clickhouse.com/docs/sql-reference/data-types) - [Statements](https://clickhouse.com/docs/sql-reference/statements) - [Operators](https://clickhouse.com/docs/sql-reference/operators) - [Engines](https://clickhouse.com/docs/engines) - [Database Engines](https://clickhouse.com/docs/engines/database-engines) - [Table Engines](https://clickhouse.com/docs/engines/table-engines) - [Functions](https://clickhouse.com/docs/sql-reference/functions) - [Regular functions](https://clickhouse.com/docs/sql-reference/functions/regular-functions) - [Aggregate functions](https://clickhouse.com/docs/sql-reference/aggregate-functions) - [Aggregate Functions](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference) - [Combinators](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators) - [Parametric](https://clickhouse.com/docs/sql-reference/aggregate-functions/parametric-functions) - [GROUPING](https://clickhouse.com/docs/sql-reference/aggregate-functions/grouping_function) - [Combinator examples](https://clickhouse.com/docs/sql-reference/aggregate-functions/grouping_function) - [Table functions](https://clickhouse.com/docs/sql-reference/table-functions) - [Window functions](https://clickhouse.com/docs/sql-reference/window-functions) - [Formats](https://clickhouse.com/docs/interfaces/formats) - [Data Lakes](https://clickhouse.com/docs/sql-reference/datalakes) - [Functions](https://clickhouse.com/docs/sql-reference/functions) - [Aggregate functions](https://clickhouse.com/docs/sql-reference/aggregate-functions) - GROUPING [Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/aggregate-functions/grouping_function.md) # GROUPING ## GROUPING[](https://clickhouse.com/docs/sql-reference/aggregate-functions/grouping_function#grouping "Direct link to GROUPING") [ROLLUP](https://clickhouse.com/docs/sql-reference/statements/select/group-by#rollup-modifier) and [CUBE](https://clickhouse.com/docs/sql-reference/statements/select/group-by#cube-modifier) are modifiers to GROUP BY. Both of these calculate subtotals. ROLLUP takes an ordered list of columns, for example `(day, month, year)`, and calculates subtotals at each level of the aggregation and then a grand total. CUBE calculates subtotals across all possible combinations of the columns specified. GROUPING identifies which rows returned by ROLLUP or CUBE are superaggregates, and which are rows that would be returned by an unmodified GROUP BY. The GROUPING function takes multiple columns as an argument, and returns a bitmask. - `1` indicates that a row returned by a `ROLLUP` or `CUBE` modifier to `GROUP BY` is a subtotal - `0` indicates that a row returned by a `ROLLUP` or `CUBE` is a row that is not a subtotal ## GROUPING SETS[](https://clickhouse.com/docs/sql-reference/aggregate-functions/grouping_function#grouping-sets "Direct link to GROUPING SETS") By default, the CUBE modifier calculates subtotals for all possible combinations of the columns passed to CUBE. GROUPING SETS allows you to specify the specific combinations to calculate. Analyzing hierarchical data is a good use case for ROLLUP, CUBE, and GROUPING SETS modifiers. The sample here is a table containing data about what Linux distribution, and the version of that distribution is installed across two datacenters. It may be valuable to look at the data by distribution, version, and location. ### Load sample data[](https://clickhouse.com/docs/sql-reference/aggregate-functions/grouping_function#load-sample-data "Direct link to Load sample data") ``` CREATE TABLE servers ( datacenter VARCHAR(255), distro VARCHAR(255) NOT NULL, version VARCHAR(50) NOT NULL, quantity INT ) ORDER BY (datacenter, distro, version) ``` ``` INSERT INTO servers(datacenter, distro, version, quantity) VALUES ('Schenectady', 'Arch','2022.08.05',50), ('Westport', 'Arch','2022.08.05',40), ('Schenectady','Arch','2021.09.01',30), ('Westport', 'Arch','2021.09.01',20), ('Schenectady','Arch','2020.05.01',10), ('Westport', 'Arch','2020.05.01',5), ('Schenectady','RHEL','9',60), ('Westport','RHEL','9',70), ('Westport','RHEL','7',80), ('Schenectady','RHEL','7',80) ``` ``` SELECT * FROM servers; ``` ``` ┌─datacenter──┬─distro─┬─version────┬─quantity─┐ │ Schenectady │ Arch │ 2020.05.01 │ 10 │ │ Schenectady │ Arch │ 2021.09.01 │ 30 │ │ Schenectady │ Arch │ 2022.08.05 │ 50 │ │ Schenectady │ RHEL │ 7 │ 80 │ │ Schenectady │ RHEL │ 9 │ 60 │ │ Westport │ Arch │ 2020.05.01 │ 5 │ │ Westport │ Arch │ 2021.09.01 │ 20 │ │ Westport │ Arch │ 2022.08.05 │ 40 │ │ Westport │ RHEL │ 7 │ 80 │ │ Westport │ RHEL │ 9 │ 70 │ └─────────────┴────────┴────────────┴──────────┘ 10 rows in set. Elapsed: 0.409 sec. ``` ### Simple queries[](https://clickhouse.com/docs/sql-reference/aggregate-functions/grouping_function#simple-queries "Direct link to Simple queries") Get the count of servers in each data center by distribution: ``` SELECT datacenter, distro, SUM (quantity) qty FROM servers GROUP BY datacenter, distro; ``` ``` ┌─datacenter──┬─distro─┬─qty─┐ │ Schenectady │ RHEL │ 140 │ │ Westport │ Arch │ 65 │ │ Schenectady │ Arch │ 90 │ │ Westport │ RHEL │ 150 │ └─────────────┴────────┴─────┘ 4 rows in set. Elapsed: 0.212 sec. ``` ``` SELECT datacenter, SUM (quantity) qty FROM servers GROUP BY datacenter; ``` ``` ┌─datacenter──┬─qty─┐ │ Westport │ 215 │ │ Schenectady │ 230 │ └─────────────┴─────┘ 2 rows in set. Elapsed: 0.277 sec. ``` ``` SELECT distro, SUM (quantity) qty FROM servers GROUP BY distro; ``` ``` ┌─distro─┬─qty─┐ │ Arch │ 155 │ │ RHEL │ 290 │ └────────┴─────┘ 2 rows in set. Elapsed: 0.352 sec. ``` ``` SELECT SUM(quantity) qty FROM servers; ``` ``` ┌─qty─┐ │ 445 │ └─────┘ 1 row in set. Elapsed: 0.244 sec. ``` ### Comparing multiple GROUP BY statements with GROUPING SETS[](https://clickhouse.com/docs/sql-reference/aggregate-functions/grouping_function#comparing-multiple-group-by-statements-with-grouping-sets "Direct link to Comparing multiple GROUP BY statements with GROUPING SETS") Breaking down the data without CUBE, ROLLUP, or GROUPING SETS: ``` SELECT datacenter, distro, SUM (quantity) qty FROM servers GROUP BY datacenter, distro UNION ALL SELECT datacenter, null, SUM (quantity) qty FROM servers GROUP BY datacenter UNION ALL SELECT null, distro, SUM (quantity) qty FROM servers GROUP BY distro UNION ALL SELECT null, null, SUM(quantity) qty FROM servers; ``` ``` ┌─datacenter─┬─distro─┬─qty─┐ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 445 │ └────────────┴────────┴─────┘ ┌─datacenter──┬─distro─┬─qty─┐ │ Westport │ ᴺᵁᴸᴸ │ 215 │ │ Schenectady │ ᴺᵁᴸᴸ │ 230 │ └─────────────┴────────┴─────┘ ┌─datacenter──┬─distro─┬─qty─┐ │ Schenectady │ RHEL │ 140 │ │ Westport │ Arch │ 65 │ │ Schenectady │ Arch │ 90 │ │ Westport │ RHEL │ 150 │ └─────────────┴────────┴─────┘ ┌─datacenter─┬─distro─┬─qty─┐ │ ᴺᵁᴸᴸ │ Arch │ 155 │ │ ᴺᵁᴸᴸ │ RHEL │ 290 │ └────────────┴────────┴─────┘ 9 rows in set. Elapsed: 0.527 sec. ``` Getting the same information using GROUPING SETS: ``` SELECT datacenter, distro, SUM (quantity) qty FROM servers GROUP BY GROUPING SETS( (datacenter,distro), (datacenter), (distro), () ) ``` ``` ┌─datacenter──┬─distro─┬─qty─┐ │ Schenectady │ RHEL │ 140 │ │ Westport │ Arch │ 65 │ │ Schenectady │ Arch │ 90 │ │ Westport │ RHEL │ 150 │ └─────────────┴────────┴─────┘ ┌─datacenter──┬─distro─┬─qty─┐ │ Westport │ │ 215 │ │ Schenectady │ │ 230 │ └─────────────┴────────┴─────┘ ┌─datacenter─┬─distro─┬─qty─┐ │ │ │ 445 │ └────────────┴────────┴─────┘ ┌─datacenter─┬─distro─┬─qty─┐ │ │ Arch │ 155 │ │ │ RHEL │ 290 │ └────────────┴────────┴─────┘ 9 rows in set. Elapsed: 0.427 sec. ``` ### Comparing CUBE with GROUPING SETS[](https://clickhouse.com/docs/sql-reference/aggregate-functions/grouping_function#comparing-cube-with-grouping-sets "Direct link to Comparing CUBE with GROUPING SETS") The CUBE in the next query, `CUBE(datacenter,distro,version)` provides a hierarchy that may not make sense. It does not make sense to look at Version across the two distributions (as Arch and RHEL do not have the same release cycle or version naming standards). The GROUPING SETS example following this one is more appropriate as it groups `distro` and `version` in the same set. ``` SELECT datacenter, distro, version, SUM(quantity) FROM servers GROUP BY CUBE(datacenter,distro,version) ORDER BY datacenter, distro; ``` ``` ┌─datacenter──┬─distro─┬─version────┬─sum(quantity)─┐ │ │ │ 7 │ 160 │ │ │ │ 2020.05.01 │ 15 │ │ │ │ 2021.09.01 │ 50 │ │ │ │ 2022.08.05 │ 90 │ │ │ │ 9 │ 130 │ │ │ │ │ 445 │ │ │ Arch │ 2021.09.01 │ 50 │ │ │ Arch │ 2022.08.05 │ 90 │ │ │ Arch │ 2020.05.01 │ 15 │ │ │ Arch │ │ 155 │ │ │ RHEL │ 9 │ 130 │ │ │ RHEL │ 7 │ 160 │ │ │ RHEL │ │ 290 │ │ Schenectady │ │ 9 │ 60 │ │ Schenectady │ │ 2021.09.01 │ 30 │ │ Schenectady │ │ 7 │ 80 │ │ Schenectady │ │ 2022.08.05 │ 50 │ │ Schenectady │ │ 2020.05.01 │ 10 │ │ Schenectady │ │ │ 230 │ │ Schenectady │ Arch │ 2022.08.05 │ 50 │ │ Schenectady │ Arch │ 2021.09.01 │ 30 │ │ Schenectady │ Arch │ 2020.05.01 │ 10 │ │ Schenectady │ Arch │ │ 90 │ │ Schenectady │ RHEL │ 7 │ 80 │ │ Schenectady │ RHEL │ 9 │ 60 │ │ Schenectady │ RHEL │ │ 140 │ │ Westport │ │ 9 │ 70 │ │ Westport │ │ 2020.05.01 │ 5 │ │ Westport │ │ 2022.08.05 │ 40 │ │ Westport │ │ 7 │ 80 │ │ Westport │ │ 2021.09.01 │ 20 │ │ Westport │ │ │ 215 │ │ Westport │ Arch │ 2020.05.01 │ 5 │ │ Westport │ Arch │ 2021.09.01 │ 20 │ │ Westport │ Arch │ 2022.08.05 │ 40 │ │ Westport │ Arch │ │ 65 │ │ Westport │ RHEL │ 9 │ 70 │ │ Westport │ RHEL │ 7 │ 80 │ │ Westport │ RHEL │ │ 150 │ └─────────────┴────────┴────────────┴───────────────┘ 39 rows in set. Elapsed: 0.355 sec. ``` Note Version in the above example may not make sense when it is not associated with a distro, if we were tracking the kernel version it might make sense because the kernel version can be associated with either distro. Using GROUPING SETS, as in the next example, may be a better choice. ``` SELECT datacenter, distro, version, SUM(quantity) FROM servers GROUP BY GROUPING SETS ( (datacenter, distro, version), (datacenter, distro)) ``` ``` ┌─datacenter──┬─distro─┬─version────┬─sum(quantity)─┐ │ Westport │ RHEL │ 9 │ 70 │ │ Schenectady │ Arch │ 2022.08.05 │ 50 │ │ Schenectady │ Arch │ 2021.09.01 │ 30 │ │ Schenectady │ RHEL │ 7 │ 80 │ │ Westport │ Arch │ 2020.05.01 │ 5 │ │ Westport │ RHEL │ 7 │ 80 │ │ Westport │ Arch │ 2021.09.01 │ 20 │ │ Westport │ Arch │ 2022.08.05 │ 40 │ │ Schenectady │ RHEL │ 9 │ 60 │ │ Schenectady │ Arch │ 2020.05.01 │ 10 │ └─────────────┴────────┴────────────┴───────────────┘ ┌─datacenter──┬─distro─┬─version─┬─sum(quantity)─┐ │ Schenectady │ RHEL │ │ 140 │ │ Westport │ Arch │ │ 65 │ │ Schenectady │ Arch │ │ 90 │ │ Westport │ RHEL │ │ 150 │ └─────────────┴────────┴─────────┴───────────────┘ 14 rows in set. Elapsed: 1.036 sec. ``` [Previous Parametric](https://clickhouse.com/docs/sql-reference/aggregate-functions/parametric-functions) [Next anyIf](https://clickhouse.com/docs/examples/aggregate-function-combinators/anyIf) - [GROUPING](https://clickhouse.com/docs/sql-reference/aggregate-functions/grouping_function#grouping) - [GROUPING SETS](https://clickhouse.com/docs/sql-reference/aggregate-functions/grouping_function#grouping-sets) - [Load sample data](https://clickhouse.com/docs/sql-reference/aggregate-functions/grouping_function#load-sample-data) - [Simple queries](https://clickhouse.com/docs/sql-reference/aggregate-functions/grouping_function#simple-queries) - [Comparing multiple GROUP BY statements with GROUPING SETS](https://clickhouse.com/docs/sql-reference/aggregate-functions/grouping_function#comparing-multiple-group-by-statements-with-grouping-sets) - [Comparing CUBE with GROUPING SETS](https://clickhouse.com/docs/sql-reference/aggregate-functions/grouping_function#comparing-cube-with-grouping-sets) Was this page helpful? ###### Try ClickHouse Cloud for FREE Separation of storage and compute, automatic scaling, built-in SQL console, and lots more. \$300 in free credits when signing up. [Try it for Free](https://console.clickhouse.cloud/signUp?loc=doc-card-banner&glxid=e936910f-4374-41f2-9fe9-b48f22a26d3a&pagePath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fgrouping_function&origPath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fgrouping_function&utm_ga=GA1.1.1997658363.1775979289) © 2016–2026 ClickHouse, Inc. [Trademark](https://clickhouse.com/legal/trademark-policy)·[Privacy](https://clickhouse.com/legal/privacy-policy)·[Security](https://trust.clickhouse.com/)·[Terms of Service](https://clickhouse.com/legal/agreements/terms-of-service) ![](https://static.scarf.sh/a.png?x-pxid=e6377503-591b-4886-9398-e69c7fee0b91) © 2016–2026 ClickHouse, Inc. [Trademark](https://clickhouse.com/legal/trademark-policy)·[Privacy](https://clickhouse.com/legal/privacy-policy)·[Security](https://trust.clickhouse.com/)·[Terms of Service](https://clickhouse.com/legal/agreements/terms-of-service) ![](https://static.scarf.sh/a.png?x-pxid=e6377503-591b-4886-9398-e69c7fee0b91) [![ClickHouse](https://clickhouse.com/docs/img/ch_logo_docs.svg)](https://clickhouse.com/) EN - Get started▼ - Cloud▼ - Manage data▼ - Server admin▼ - Reference▼ - Integrations▼ - ClickStack▼ - chDB▼ - About▼ [![ClickHouse](https://clickhouse.com/docs/img/ch_logo_docs.svg)](https://clickhouse.com/) EN main-menu - Introduction▼ - [Syntax](https://clickhouse.com/docs/sql-reference/syntax) - [Input and Output Formats](https://clickhouse.com/docs/sql-reference/formats) - Data types▼ - Statements▼ - Operators▼ - Engines▼ - Functions▼ - Regular functions▼ - Aggregate functions▼ - Aggregate Functions▼ - [Combinators](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators) - [Parametric](https://clickhouse.com/docs/sql-reference/aggregate-functions/parametric-functions) - [GROUPING](https://clickhouse.com/docs/sql-reference/aggregate-functions/grouping_function) - Combinator examples▼ - Table functions▼ - Window functions▼ - Formats▼ - [Data Lakes](https://clickhouse.com/docs/sql-reference/datalakes)
Readable Markdown	## GROUPING[](https://clickhouse.com/docs/sql-reference/aggregate-functions/grouping_function#grouping "Direct link to GROUPING") [ROLLUP](https://clickhouse.com/docs/sql-reference/statements/select/group-by#rollup-modifier) and [CUBE](https://clickhouse.com/docs/sql-reference/statements/select/group-by#cube-modifier) are modifiers to GROUP BY. Both of these calculate subtotals. ROLLUP takes an ordered list of columns, for example `(day, month, year)`, and calculates subtotals at each level of the aggregation and then a grand total. CUBE calculates subtotals across all possible combinations of the columns specified. GROUPING identifies which rows returned by ROLLUP or CUBE are superaggregates, and which are rows that would be returned by an unmodified GROUP BY. The GROUPING function takes multiple columns as an argument, and returns a bitmask. - `1` indicates that a row returned by a `ROLLUP` or `CUBE` modifier to `GROUP BY` is a subtotal - `0` indicates that a row returned by a `ROLLUP` or `CUBE` is a row that is not a subtotal ## GROUPING SETS[](https://clickhouse.com/docs/sql-reference/aggregate-functions/grouping_function#grouping-sets "Direct link to GROUPING SETS") By default, the CUBE modifier calculates subtotals for all possible combinations of the columns passed to CUBE. GROUPING SETS allows you to specify the specific combinations to calculate. Analyzing hierarchical data is a good use case for ROLLUP, CUBE, and GROUPING SETS modifiers. The sample here is a table containing data about what Linux distribution, and the version of that distribution is installed across two datacenters. It may be valuable to look at the data by distribution, version, and location. ### Load sample data[](https://clickhouse.com/docs/sql-reference/aggregate-functions/grouping_function#load-sample-data "Direct link to Load sample data") ``` CREATE TABLE servers ( datacenter VARCHAR(255), distro VARCHAR(255) NOT NULL, version VARCHAR(50) NOT NULL, quantity INT ) ORDER BY (datacenter, distro, version) ``` ``` INSERT INTO servers(datacenter, distro, version, quantity) VALUES ('Schenectady', 'Arch','2022.08.05',50), ('Westport', 'Arch','2022.08.05',40), ('Schenectady','Arch','2021.09.01',30), ('Westport', 'Arch','2021.09.01',20), ('Schenectady','Arch','2020.05.01',10), ('Westport', 'Arch','2020.05.01',5), ('Schenectady','RHEL','9',60), ('Westport','RHEL','9',70), ('Westport','RHEL','7',80), ('Schenectady','RHEL','7',80) ``` ``` SELECT * FROM servers; ``` ``` ┌─datacenter──┬─distro─┬─version────┬─quantity─┐ │ Schenectady │ Arch │ 2020.05.01 │ 10 │ │ Schenectady │ Arch │ 2021.09.01 │ 30 │ │ Schenectady │ Arch │ 2022.08.05 │ 50 │ │ Schenectady │ RHEL │ 7 │ 80 │ │ Schenectady │ RHEL │ 9 │ 60 │ │ Westport │ Arch │ 2020.05.01 │ 5 │ │ Westport │ Arch │ 2021.09.01 │ 20 │ │ Westport │ Arch │ 2022.08.05 │ 40 │ │ Westport │ RHEL │ 7 │ 80 │ │ Westport │ RHEL │ 9 │ 70 │ └─────────────┴────────┴────────────┴──────────┘ 10 rows in set. Elapsed: 0.409 sec. ``` ### Simple queries[](https://clickhouse.com/docs/sql-reference/aggregate-functions/grouping_function#simple-queries "Direct link to Simple queries") Get the count of servers in each data center by distribution: ``` SELECT datacenter, distro, SUM (quantity) qty FROM servers GROUP BY datacenter, distro; ``` ``` ┌─datacenter──┬─distro─┬─qty─┐ │ Schenectady │ RHEL │ 140 │ │ Westport │ Arch │ 65 │ │ Schenectady │ Arch │ 90 │ │ Westport │ RHEL │ 150 │ └─────────────┴────────┴─────┘ 4 rows in set. Elapsed: 0.212 sec. ``` ``` SELECT datacenter, SUM (quantity) qty FROM servers GROUP BY datacenter; ``` ``` ┌─datacenter──┬─qty─┐ │ Westport │ 215 │ │ Schenectady │ 230 │ └─────────────┴─────┘ 2 rows in set. Elapsed: 0.277 sec. ``` ``` SELECT distro, SUM (quantity) qty FROM servers GROUP BY distro; ``` ``` ┌─distro─┬─qty─┐ │ Arch │ 155 │ │ RHEL │ 290 │ └────────┴─────┘ 2 rows in set. Elapsed: 0.352 sec. ``` ``` SELECT SUM(quantity) qty FROM servers; ``` ``` ┌─qty─┐ │ 445 │ └─────┘ 1 row in set. Elapsed: 0.244 sec. ``` ### Comparing multiple GROUP BY statements with GROUPING SETS[](https://clickhouse.com/docs/sql-reference/aggregate-functions/grouping_function#comparing-multiple-group-by-statements-with-grouping-sets "Direct link to Comparing multiple GROUP BY statements with GROUPING SETS") Breaking down the data without CUBE, ROLLUP, or GROUPING SETS: ``` SELECT datacenter, distro, SUM (quantity) qty FROM servers GROUP BY datacenter, distro UNION ALL SELECT datacenter, null, SUM (quantity) qty FROM servers GROUP BY datacenter UNION ALL SELECT null, distro, SUM (quantity) qty FROM servers GROUP BY distro UNION ALL SELECT null, null, SUM(quantity) qty FROM servers; ``` ``` ┌─datacenter─┬─distro─┬─qty─┐ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 445 │ └────────────┴────────┴─────┘ ┌─datacenter──┬─distro─┬─qty─┐ │ Westport │ ᴺᵁᴸᴸ │ 215 │ │ Schenectady │ ᴺᵁᴸᴸ │ 230 │ └─────────────┴────────┴─────┘ ┌─datacenter──┬─distro─┬─qty─┐ │ Schenectady │ RHEL │ 140 │ │ Westport │ Arch │ 65 │ │ Schenectady │ Arch │ 90 │ │ Westport │ RHEL │ 150 │ └─────────────┴────────┴─────┘ ┌─datacenter─┬─distro─┬─qty─┐ │ ᴺᵁᴸᴸ │ Arch │ 155 │ │ ᴺᵁᴸᴸ │ RHEL │ 290 │ └────────────┴────────┴─────┘ 9 rows in set. Elapsed: 0.527 sec. ``` Getting the same information using GROUPING SETS: ``` SELECT datacenter, distro, SUM (quantity) qty FROM servers GROUP BY GROUPING SETS( (datacenter,distro), (datacenter), (distro), () ) ``` ``` ┌─datacenter──┬─distro─┬─qty─┐ │ Schenectady │ RHEL │ 140 │ │ Westport │ Arch │ 65 │ │ Schenectady │ Arch │ 90 │ │ Westport │ RHEL │ 150 │ └─────────────┴────────┴─────┘ ┌─datacenter──┬─distro─┬─qty─┐ │ Westport │ │ 215 │ │ Schenectady │ │ 230 │ └─────────────┴────────┴─────┘ ┌─datacenter─┬─distro─┬─qty─┐ │ │ │ 445 │ └────────────┴────────┴─────┘ ┌─datacenter─┬─distro─┬─qty─┐ │ │ Arch │ 155 │ │ │ RHEL │ 290 │ └────────────┴────────┴─────┘ 9 rows in set. Elapsed: 0.427 sec. ``` ### Comparing CUBE with GROUPING SETS[](https://clickhouse.com/docs/sql-reference/aggregate-functions/grouping_function#comparing-cube-with-grouping-sets "Direct link to Comparing CUBE with GROUPING SETS") The CUBE in the next query, `CUBE(datacenter,distro,version)` provides a hierarchy that may not make sense. It does not make sense to look at Version across the two distributions (as Arch and RHEL do not have the same release cycle or version naming standards). The GROUPING SETS example following this one is more appropriate as it groups `distro` and `version` in the same set. ``` SELECT datacenter, distro, version, SUM(quantity) FROM servers GROUP BY CUBE(datacenter,distro,version) ORDER BY datacenter, distro; ``` ``` ┌─datacenter──┬─distro─┬─version────┬─sum(quantity)─┐ │ │ │ 7 │ 160 │ │ │ │ 2020.05.01 │ 15 │ │ │ │ 2021.09.01 │ 50 │ │ │ │ 2022.08.05 │ 90 │ │ │ │ 9 │ 130 │ │ │ │ │ 445 │ │ │ Arch │ 2021.09.01 │ 50 │ │ │ Arch │ 2022.08.05 │ 90 │ │ │ Arch │ 2020.05.01 │ 15 │ │ │ Arch │ │ 155 │ │ │ RHEL │ 9 │ 130 │ │ │ RHEL │ 7 │ 160 │ │ │ RHEL │ │ 290 │ │ Schenectady │ │ 9 │ 60 │ │ Schenectady │ │ 2021.09.01 │ 30 │ │ Schenectady │ │ 7 │ 80 │ │ Schenectady │ │ 2022.08.05 │ 50 │ │ Schenectady │ │ 2020.05.01 │ 10 │ │ Schenectady │ │ │ 230 │ │ Schenectady │ Arch │ 2022.08.05 │ 50 │ │ Schenectady │ Arch │ 2021.09.01 │ 30 │ │ Schenectady │ Arch │ 2020.05.01 │ 10 │ │ Schenectady │ Arch │ │ 90 │ │ Schenectady │ RHEL │ 7 │ 80 │ │ Schenectady │ RHEL │ 9 │ 60 │ │ Schenectady │ RHEL │ │ 140 │ │ Westport │ │ 9 │ 70 │ │ Westport │ │ 2020.05.01 │ 5 │ │ Westport │ │ 2022.08.05 │ 40 │ │ Westport │ │ 7 │ 80 │ │ Westport │ │ 2021.09.01 │ 20 │ │ Westport │ │ │ 215 │ │ Westport │ Arch │ 2020.05.01 │ 5 │ │ Westport │ Arch │ 2021.09.01 │ 20 │ │ Westport │ Arch │ 2022.08.05 │ 40 │ │ Westport │ Arch │ │ 65 │ │ Westport │ RHEL │ 9 │ 70 │ │ Westport │ RHEL │ 7 │ 80 │ │ Westport │ RHEL │ │ 150 │ └─────────────┴────────┴────────────┴───────────────┘ 39 rows in set. Elapsed: 0.355 sec. ``` Note Version in the above example may not make sense when it is not associated with a distro, if we were tracking the kernel version it might make sense because the kernel version can be associated with either distro. Using GROUPING SETS, as in the next example, may be a better choice. ``` SELECT datacenter, distro, version, SUM(quantity) FROM servers GROUP BY GROUPING SETS ( (datacenter, distro, version), (datacenter, distro)) ``` ``` ┌─datacenter──┬─distro─┬─version────┬─sum(quantity)─┐ │ Westport │ RHEL │ 9 │ 70 │ │ Schenectady │ Arch │ 2022.08.05 │ 50 │ │ Schenectady │ Arch │ 2021.09.01 │ 30 │ │ Schenectady │ RHEL │ 7 │ 80 │ │ Westport │ Arch │ 2020.05.01 │ 5 │ │ Westport │ RHEL │ 7 │ 80 │ │ Westport │ Arch │ 2021.09.01 │ 20 │ │ Westport │ Arch │ 2022.08.05 │ 40 │ │ Schenectady │ RHEL │ 9 │ 60 │ │ Schenectady │ Arch │ 2020.05.01 │ 10 │ └─────────────┴────────┴────────────┴───────────────┘ ┌─datacenter──┬─distro─┬─version─┬─sum(quantity)─┐ │ Schenectady │ RHEL │ │ 140 │ │ Westport │ Arch │ │ 65 │ │ Schenectady │ Arch │ │ 90 │ │ Westport │ RHEL │ │ 150 │ └─────────────┴────────┴─────────┴───────────────┘ 14 rows in set. Elapsed: 1.036 sec. ```
Shard	89 (laksa)
Root Hash	12633450985039531489
Unparsed URL	com,clickhouse!/docs/sql-reference/aggregate-functions/grouping_function s443