🕷️ Crawler Inspector

URL Lookup

Direct Parameter Lookup

Raw Queries and Responses

1. Shard Calculation

Query:

Response:

Calculated Shard: 166 (from laksa005)

2. Crawled Status Check

Query:

curl -X POST \
  'http://laksa166.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://chistadata.com/groupby-groupings-rollups-cubes-clickhouse/\')), getAhrefsUnparsedNoserviceFromURL(\'https://chistadata.com/groupby-groupings-rollups-cubes-clickhouse/\'))) FORMAT JSONEachRow'

Response:

{"found_url":"https:\/\/chistadata.com\/groupby-groupings-rollups-cubes-clickhouse\/","crawl_time":1775792559,"first_indexed_time":1709670550,"http_code":200,"src_unparsed":"com,chistadata!\/groupby-groupings-rollups-cubes-clickhouse\/ s443","src_root_hash":"8420801914178628366","history_drop_reason":null,"meta_title":"Using GROUPBY for Groupings, Rolllups and Cubes in ClickHouse","meta_descriptions":["In this article, we explore how ClickHouse operations such as grouping, rollup, and cube are implemented using the GROUPBY clause"],"attrs_boilerpipe_text":"Table of Contents\nIntroduction\nExample 1: Sales Data\nExample 2: Web Traffic Data\nExample 3: Employee Data\nConclusion\nYou might also like:\nIntroduction\nGrouping, rollup, and cube are SQL query operations that allow for grouping and aggregation of data based on multiple dimensions or attributes. In ClickHouse, these operations are implemented using the GROUP BY clause, which allows you to group data based on one or more columns. Here are some real-life data examples to illustrate how to implement groupings, rollups, and cubes in ClickHouse:\nExample 1: Sales Data\nSuppose we have a sales table with the following columns: order_id, customer_id, order_date, product_id, and quantity. We want to calculate the total quantity sold for each product and each month. Here’s how we can do this using grouping:\nSELECT product_id, toMonth(order_date) AS month, sum(quantity) AS total_quantity\nFROM sales\nGROUP BY product_id, month\nORDER BY product_id, month\nThis query will group the sales data by product_id and month, and calculate the total quantity sold for each combination of product and month. The toMonth() function is used to extract the month from the order_date column.\nExample 2: Web Traffic Data\nSuppose we have a web traffic table with the following columns: timestamp, ip_address, page_url, user_agent. We want to calculate the number of page views by browser type and operating system. Here’s how we can do this using rollup:\nSELECT\nCASE\nWHEN user_agent LIKE ‘%Firefox%’ THEN ‘Firefox’\nWHEN user_agent LIKE ‘%Chrome%’ THEN ‘Chrome’\nELSE ‘Other’\nEND AS browser,\nCASE\nWHEN user_agent LIKE ‘%Windows%’ THEN ‘Windows’\nWHEN user_agent LIKE ‘%Mac OS%’ THEN ‘Mac OS’\nELSE ‘Other’\nEND AS os,\ncount(*) AS page_views\nFROM web_traffic\nGROUP BY ROLLUP(browser, os)\nORDER BY browser, os\nThis query will group the web traffic data by browser and operating system, and calculate the number of page views for each combination. The ROLLUP() function is used to create a hierarchy of subtotals, so the query will also return subtotals for each browser and for each operating system.\nExample 3: Employee Data\nSuppose we have an employee table with the following columns: employee_id, department, job_title, salary. We want to calculate the average salary by department and job title, and also calculate subtotals by department and totals for all employees. Here’s how we can do this using cube:\nSELECT department, job_title, avg(salary) AS avg_salary\nFROM employees\nGROUP BY CUBE(department, job_title)\nORDER BY department, job_title\nThis query will group the employee data by department and job title, and calculate the average salary for each combination. The CUBE() function is used to create a hierarchy of subtotals and totals, so the query will also return subtotals by department and totals for all employees.\nConclusion\nIn summary, grouping, rollup, and cube are powerful SQL query operations that allow for grouping and aggregation of data based on multiple dimensions or attributes. In ClickHouse, these operations are implemented using the GROUP BY clause, along with functions such as ROLLUP() and CUBE(). By using these operations, you can gain deeper insights into your data and perform complex analysis on large-scale data sets.\nTo read more about GROUPBY & the EXPLAIN tool in ClickHouse, do consider reading the below articles\nClickHouse Performance: Optimizing HASH GROUP BY and ORDER BY Queries\nClickHouse EXPLAIN: Determine JOIN Order in Query Execution Plans\nComprehensive Guide to ClickHouse EXPLAIN\nYou might also like:\nClickHouse vs Snowflake: Choosing the Right Data Analytics Platform for Your Business\nComprehensive Guide for ClickHouse Horizontal Scaling and Capacity Planning\nAggregate Functions via ClickHouse\nGenerative AI in Analytics: Part 1 – Unlocking New Possibilities with ClickHouse\nHow to Implement Partial Indexes in ClickHouse","attrs_markdown":"[ChistaDATA Inc. Enterprise-class 24\\*7 ClickHouse Consultative Support and Managed Services](https:\/\/chistadata.com\/ \"ChistaDATA Inc.\")\n\n- [ChistaDATA](https:\/\/chistadata.com\/)\n  - [ClickHouse®](https:\/\/chistadata.com\/understanding-clickhouse\/)\n  - [MergeTree](https:\/\/chistadata.com\/mergetree\/)\n  - [Why is ClickHouse So Fast](https:\/\/chistadata.com\/why-clickhouse-is-so-fast-2\/)\n  - [Columnar Stores](https:\/\/chistadata.com\/analyzing-performance-exploring-columnar-stores-vs-row-based-databases\/)\n  - [Vectorized Query](https:\/\/chistadata.com\/clickhouses-vectorized-query-processing-unleashing-columnar-power\/)\n  - [For CTOs](https:\/\/chistadata.com\/chistadata-cto\/)\n  - [Data Warehousing](https:\/\/chistadata.com\/database-warehousing-support\/)\n- [Engineering](https:\/\/chistadata.com\/chistadata-server-for-clickhouse\/)\n  - [Real-Time Analytics](https:\/\/chistadata.com\/realtimeanalytics-success-partnering-with-chistadata-to-build-optimal-scalable-and-reliable-solutions-on-clickhouse\/)\n  - [Break Fix Engineering](https:\/\/chistadata.com\/chistadata-break-fix-services-clickhouse-support\/)\n  - [Data Archiving](https:\/\/chistadata.com\/unlocking-the-power-of-clickhouse-from-real-time-analytics-to-reliable-archive-storage\/)\n  - [Cloud Native ClickHouse](https:\/\/chistadata.com\/custom-data-fabric-solutions-data-fabric\/)\n  - [ClickHouse Consulting](https:\/\/chistadata.com\/clickhouse-consulting\/)\n    - [Performance Audit](https:\/\/chistadata.com\/clickhouse-performance-audit-for-performance\/)\n      - [Pre- Engagement Questionnaire](https:\/\/chistadata.com\/clickhouse-performance-audit\/)\n  - [ClickHouse Strategy](https:\/\/chistadata.com\/data-strategy-how-to-build-one\/)\n  - [Online Ticketing System](https:\/\/support.chistadata.com\/)\n- [Support](https:\/\/chistadata.com\/clickhouse-support\/)\n  - [ClickHouse Migration](https:\/\/chistadata.com\/clickhouse-migration\/)\n  - [ClickHouse Audit](https:\/\/chistadata.com\/clickhouse-performance-audit-recommendation\/)\n  - [Real-Time Analytics](https:\/\/chistadata.com\/real-time-analytics-with-clickhouse\/)\n  - [Data Warehousing Support](https:\/\/chistadata.com\/database-warehousing-support\/)\n  - [Data Analytics](https:\/\/chistadata.com\/from-batch-processing-complexities-to-clickhouse-excellence-simplifying-data-analytics-with-chistadata\/)\n  - [ChistaDATA Analytics](https:\/\/chistadata.com\/high-performance-real-time-analytics\/)\n  - [Gen AI](https:\/\/chistadata.com\/chistadata-gen-ai-support-with-clickhouse\/)\n  - [Online Ticketing System](https:\/\/support.chistadata.com\/)\n- [Managed Services](https:\/\/chistadata.com\/clickhouse-services\/)\n  - [ClickHouse Services](https:\/\/chistadata.com\/clickhouse-managed-services\/)\n  - [DBA Services](https:\/\/chistadata.com\/fully-managed-clickhouse-services\/)\n  - [ClickHouse Performance](https:\/\/chistadata.com\/clickhouse-performance-tuning-and-optimization\/)\n  - [Data Strategy](https:\/\/chistadata.com\/crafting-right-data-strategy\/)\n  - [ClickHouse Analytics](https:\/\/chistadata.com\/unlock-insights-chistadata-real-time-analytics\/)\n  - [Data Archiving](https:\/\/chistadata.com\/rdbms-archival-chistadata-fabric\/)\n  - [DBaaS Optimization](https:\/\/chistadata.com\/dbaas-optimisation\/)\n  - [Data SRE](https:\/\/chistadata.com\/data-sre\/)\n  - [Online Ticketing System](https:\/\/support.chistadata.com\/)\n- [Blog](https:\/\/chistadata.com\/blog\/)\n  - [Shiv Iyer Talks](https:\/\/speakerdeck.com\/shiviyer007)\n  - [ChistaDATA Blog](https:\/\/chistadata.com\/blog\/)\n- [University](https:\/\/chistadata.com\/chistadata-university\/)\n- [Careers](https:\/\/chistadata.com\/careers\/)\n- [Contact](https:\/\/chistadata.com\/contact-chistadata-inc\/)\n\n- [Twitter](https:\/\/twitter.com\/chistadata)\n- [Facebook](https:\/\/www.facebook.com\/ChistaDATAOfficial\/)\n- [LinkedIn](https:\/\/www.linkedin.com\/company\/chistadata)\n  - [Shiv Iyer](https:\/\/www.linkedin.com\/in\/thewebscaledba\/recent-activity\/articles\/)\n- [GitHub](https:\/\/github.com\/ChistaDATA)\n  - [@ShivIyer](https:\/\/github.com\/shiviyer\/Blogs\/wiki)\n\n[Home](https:\/\/chistadata.com\/ \"Home\")\n\n[ClickHouse](https:\/\/chistadata.com\/category\/clickhouse\/ \"ClickHouse\")\n\nUsing GROUPBY for Groupings, Rollups and Cubes in ClickHouse\n\n# Using GROUPBY for Groupings, Rollups and Cubes in ClickHouse\n[Shiv Iyer](https:\/\/chistadata.com\/author\/chistadata\/) [ClickHouse](https:\/\/chistadata.com\/category\/clickhouse\/), [ChistaDATA](https:\/\/chistadata.com\/category\/chistadata\/), [ClickHouse DBA Script](https:\/\/chistadata.com\/category\/clickhouse-dba-script\/), [ClickHouse DBA Support](https:\/\/chistadata.com\/category\/clickhouse-dba-support\/), [ClickHouse Internals](https:\/\/chistadata.com\/category\/clickhouse-internals\/), [ClickHouse Performance](https:\/\/chistadata.com\/category\/clickhouse-performance\/), [ClickHouse Reliability](https:\/\/chistadata.com\/category\/clickhouse-reliability\/)\n\nTable of Contents\n\n[Toggle](https:\/\/chistadata.com\/groupby-groupings-rollups-cubes-clickhouse\/)\n\n- [Introduction](https:\/\/chistadata.com\/groupby-groupings-rollups-cubes-clickhouse\/#Introduction)\n- [Example 1: Sales Data](https:\/\/chistadata.com\/groupby-groupings-rollups-cubes-clickhouse\/#Example_1_Sales_Data)\n- [Example 2: Web Traffic Data](https:\/\/chistadata.com\/groupby-groupings-rollups-cubes-clickhouse\/#Example_2_Web_Traffic_Data)\n- [Example 3: Employee Data](https:\/\/chistadata.com\/groupby-groupings-rollups-cubes-clickhouse\/#Example_3_Employee_Data)\n- [Conclusion](https:\/\/chistadata.com\/groupby-groupings-rollups-cubes-clickhouse\/#Conclusion)\n  - [You might also like:](https:\/\/chistadata.com\/groupby-groupings-rollups-cubes-clickhouse\/#You_might_also_like)\n## Introduction\nGrouping, rollup, and cube are SQL query operations that allow for grouping and aggregation of data based on multiple dimensions or attributes. In ClickHouse, these operations are implemented using the GROUP BY clause, which allows you to group data based on one or more columns. Here are some real-life data examples to illustrate how to implement groupings, rollups, and cubes in ClickHouse:\n\n## Example 1: Sales Data\nSuppose we have a sales table with the following columns: order\\_id, customer\\_id, order\\_date, product\\_id, and quantity. We want to calculate the total quantity sold for each product and each month. Here’s how we can do this using grouping:\n```\nSELECT product_id, toMonth(order_date) AS month, sum(quantity) AS total_quantity\nFROM sales\nGROUP BY product_id, month\nORDER BY product_id, month\n```\nThis query will group the sales data by product\\_id and month, and calculate the total quantity sold for each combination of product and month. The toMonth() function is used to extract the month from the order\\_date column.\n\n## **Example 2: Web Traffic Data**\nSuppose we have a web traffic table with the following columns: timestamp, ip\\_address, page\\_url, user\\_agent. We want to calculate the number of page views by browser type and operating system. Here’s how we can do this using rollup:\n```\nSELECT\nCASE\nWHEN user_agent LIKE ‘%Firefox%’ THEN ‘Firefox’\nWHEN user_agent LIKE ‘%Chrome%’ THEN ‘Chrome’\nELSE ‘Other’\nEND AS browser,\nCASE\nWHEN user_agent LIKE ‘%Windows%’ THEN ‘Windows’\nWHEN user_agent LIKE ‘%Mac OS%’ THEN ‘Mac OS’\nELSE ‘Other’\nEND AS os,\ncount(*) AS page_views\nFROM web_traffic\nGROUP BY ROLLUP(browser, os)\nORDER BY browser, os\n```\nThis query will group the web traffic data by browser and operating system, and calculate the number of page views for each combination. The ROLLUP() function is used to create a hierarchy of subtotals, so the query will also return subtotals for each browser and for each operating system.\n\n## **Example 3: Employee Data**\nSuppose we have an employee table with the following columns: employee\\_id, department, job\\_title, salary. We want to calculate the average salary by department and job title, and also calculate subtotals by department and totals for all employees. Here’s how we can do this using cube:\n```\nSELECT department, job_title, avg(salary) AS avg_salary\nFROM employees\nGROUP BY CUBE(department, job_title)\nORDER BY department, job_title\n```\nThis query will group the employee data by department and job title, and calculate the average salary for each combination. The CUBE() function is used to create a hierarchy of subtotals and totals, so the query will also return subtotals by department and totals for all employees.\n\n## Conclusion\nIn summary, grouping, rollup, and cube are powerful SQL query operations that allow for grouping and aggregation of data based on multiple dimensions or attributes. In ClickHouse, these operations are implemented using the GROUP BY clause, along with functions such as ROLLUP() and CUBE(). By using these operations, you can gain deeper insights into your data and perform complex analysis on large-scale data sets.\n\nTo read more about GROUPBY & the EXPLAIN tool in ClickHouse, do consider reading the below articles\n\n- [ClickHouse Performance: Optimizing HASH GROUP BY and ORDER BY Queries](https:\/\/chistadata.com\/optimizing-hash-group-by-and-order-by-queries-in-clickhouse-strategies-for-enhanced-performance\/)\n- [ClickHouse EXPLAIN: Determine JOIN Order in Query Execution Plans](https:\/\/chistadata.com\/how-to-determine-the-join-order-in-a-clickhouse-execution-plan\/)\n- [Comprehensive Guide to ClickHouse EXPLAIN](https:\/\/chistadata.com\/analyse-queries-in-clickhouse\/)\n### You might also like:\n- [ClickHouse vs Snowflake: Choosing the Right Data Analytics Platform for Your Business](https:\/\/chistadata.com\/clickhouse-vs-snowflake-choose-right-data-analytics\/)\n- [Comprehensive Guide for ClickHouse Horizontal Scaling and Capacity Planning](https:\/\/chistadata.com\/clickhouse-horizontal-scaling-capacity-planning\/)\n- [Aggregate Functions via ClickHouse](https:\/\/chistadata.com\/aggregate-functions-via-clickhouse\/)\n- [Generative AI in Analytics: Part 1 – Unlocking New Possibilities with ClickHouse](https:\/\/chistadata.com\/generative-ai-analytics-with-clickhouse\/)\n- [How to Implement Partial Indexes in ClickHouse](https:\/\/chistadata.com\/how-to-implement-partial-indexes-in-clickhouse\/)\n\n![](https:\/\/secure.gravatar.com\/avatar\/6becbb82f32acaaa1c0d6efde7a3863d0d17d5dd0ef64be2421b64d88273c7c0?s=125&d=mm&r=g)\n\nAbout Shiv Iyer    [271 Articles](https:\/\/chistadata.com\/author\/chistadata\/ \"More articles written by Shiv Iyer'\")\n\nOpen Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally.\n\n[Website](https:\/\/chistadata.com%20 \"Visit the website of Shiv Iyer\")   [Facebook](https:\/\/www.facebook.com\/ChistaDATAOfficial\/ \"Follow Shiv Iyer on Facebook\")   [Twitter](https:\/\/twitter.com\/thewebscaledba \"Follow Shiv Iyer on Twitter\")   [LinkedIn](https:\/\/www.linkedin.com\/in\/thewebscaledba\/ \"Follow Shiv Iyer on LinkedIn\")\n\n[![Comprehensive Guide to ChistaDATA's ClickHouse Performance Audit](https:\/\/chistadata.com\/wp-content\/uploads\/2023\/05\/Comprehensive-Guide-to-ChistaDATAs-ClickHouse-Performance-Audit--80x60.jpeg)PreviousComprehensive Guide to ChistaDATA’s ClickHouse Performance Audit](https:\/\/chistadata.com\/chistadata-clickhouse-performance-audit-guide\/)\n\n[![Implementing Parallel Replicas with Dynamic Shards](https:\/\/chistadata.com\/wp-content\/uploads\/2023\/05\/shutterstock_155390825-80x60.jpg)NextClickHouse Horizontal Scaling: Implementing Parallel Replicas with Dynamic Shards](https:\/\/chistadata.com\/parallel-replicas-with-dynamic-shards-horizontal-scaling-clickhouse\/)\n\n#### ChistaDATA is committed to open source software and building high performance ColumnStores\n*In the spirit of freedom, independence and innovation. **ChistaDATA Corporation** is not affiliated with **ClickHouse Corporation***\n#### Tell us how we can help\\!\n#### Search ChistaDATA Website\n#### ★READ THIS WARNING★\n**\\* Everything changes over time –** *Our* *blogs\/posts and comments changes over time, That’s how it should be! Whatever we comment from ChistaDATA Inc. Teams (including Shiv Iyer) and other stakeholders or guest bloggers posted here are never permanent, These things worked for us. But, there is no guarantee they will work for you too, When using the recommendations from ChistaDATA or MinervaDB or MinervaSQL or any other online resources \/ Google, You must* **test** *the advice before applying them to your production systems, and* **always invest for a robust Database DR solution,** *Thank you for understanding.*\n\n#### Recent Posts from ChistaDATA\n- [Advanced eBPF-Based Performance Analysis for ClickHouse](https:\/\/chistadata.com\/ebpf-performance-analysis-clickhouse\/)\n- [ClickHouse Performance Detective’s Toolkit](https:\/\/chistadata.com\/clickhouse-performance-toolkit\/)\n- [How MergeTree Internals Drive Query Latency](https:\/\/chistadata.com\/mergetree-internals-query-latency\/)\n- [ClickHouse Thread Contention and Troubleshooting](https:\/\/chistadata.com\/clickhouse-thread-contention-and-troubleshooting\/)\n- [Designing ClickHouse for Mixed Workloads](https:\/\/chistadata.com\/designing-clickhouse-for-mixed-workloads\/)\n\n#### ☎ TOLL FREE PHONE (24\\*7)\n### **(844)395-5717**\n\n#### 🚩 ChistaDATA Inc. FAX\n### **\\+1 (209) 314-2364**\n\n#### CORPORATE ADDRESS: CALIFORNIA\n**ChistaDATA Inc.** 440 N BARRANCA AVE \\#9718 COVINA,  \n CA 91723\n\n#### CORPORATE ADDRESS: NEW CASTLE, DELAWARE\n**ChistaDATA Inc.,**  \n 256 Chapman Road STE 105-4,  \n Newark, New Castle 19702,  \n Delaware\n\n#### CORPORATE ADDRESS: DELAWARE\n**ChistaDATA Inc**.,\n\nPO Box 2093 PHILADELPHIA PIKE \\#3339\n\nCLAYMONT, DE 19703\n\n#### HOW CAN WE HELP?\nWe are committed to building Optimal, Scalable, Highly Available, Reliable, Fault-Tolerant and Secured Database Infrastructure Operations for WebScale to our customers globally\n\n#### CHISTADATA IS COMMITTED TO OPEN SOURCE SOFTWARE AND BUILDING HIGH PERFORMANCE COLUMNSTORES\n*In the spirit of freedom, independence and innovation. **ChistaDATA Corporation** is not affiliated with **ClickHouse Corporation***\n\n#### ChistaDATA Inc. Knowledge base is licensed under the Apache License, Version 2.0 (the “License”)\nCopyright 2022 ChistaDATA Inc\n\nLicensed under the Apache License, Version 2.0 (the “License”); you may not use this file except in compliance with the License. You may obtain a copy of the License at\n\nhttp:\/\/www.apache.org\/licenses\/LICENSE-2.0\n\nUnless required by applicable law or agreed to in writing, software distributed under the License is distributed on an “AS IS” BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.\n\nPostgreSQL is a registered trademark of the PostgreSQL Community Association. ClickHouse is a registered trademark of ClickHouse, Inc. MongoDB is a registered trademark of MongoDB, Inc. Couchbase is a registered trademark of Couchbase, Inc. Redis is a registered trademark of Redis Ltd. Apache Cassandra is a registered trademark of the Apache Software Foundation. Milvus is a registered trademark of Zilliz. MinIO is a registered trademark of MinIO, Inc. Amazon Redshift and Amazon Aurora are registered trademarks of \\[Amazon.com\\](http:\/\/amazon.com\/), Inc. Google Cloud is a registered trademark of Google LLC. Snowflake is a registered trademark of Snowflake Inc. Databricks is a registered trademark of Databricks, Inc. MySQL and InnoDB are registered trademarks of Oracle Corporation. MariaDB is a trademark of MariaDB Corporation Ab. All other trademarks are the property of their respective owners. Any other product or company names mentioned may be trademarks or trade names of their respective owners. Copyright © 2010–2026. All Rights Reserved by ChistaDATA®.\n\nTable of Contents\n\n[×](https:\/\/chistadata.com\/groupby-groupings-rollups-cubes-clickhouse\/)\n\n- [Introduction](https:\/\/chistadata.com\/groupby-groupings-rollups-cubes-clickhouse\/#Introduction)\n- [Example 1: Sales Data](https:\/\/chistadata.com\/groupby-groupings-rollups-cubes-clickhouse\/#Example_1_Sales_Data)\n- [Example 2: Web Traffic Data](https:\/\/chistadata.com\/groupby-groupings-rollups-cubes-clickhouse\/#Example_2_Web_Traffic_Data)\n- [Example 3: Employee Data](https:\/\/chistadata.com\/groupby-groupings-rollups-cubes-clickhouse\/#Example_3_Employee_Data)\n- [Conclusion](https:\/\/chistadata.com\/groupby-groupings-rollups-cubes-clickhouse\/#Conclusion)\n  - [You might also like:](https:\/\/chistadata.com\/groupby-groupings-rollups-cubes-clickhouse\/#You_might_also_like)\n\n[→ Index](https:\/\/chistadata.com\/groupby-groupings-rollups-cubes-clickhouse\/)","attrs_readable_markdown":"Table of Contents\n\n- [Introduction](https:\/\/chistadata.com\/groupby-groupings-rollups-cubes-clickhouse\/#Introduction)\n- [Example 1: Sales Data](https:\/\/chistadata.com\/groupby-groupings-rollups-cubes-clickhouse\/#Example_1_Sales_Data)\n- [Example 2: Web Traffic Data](https:\/\/chistadata.com\/groupby-groupings-rollups-cubes-clickhouse\/#Example_2_Web_Traffic_Data)\n- [Example 3: Employee Data](https:\/\/chistadata.com\/groupby-groupings-rollups-cubes-clickhouse\/#Example_3_Employee_Data)\n- [Conclusion](https:\/\/chistadata.com\/groupby-groupings-rollups-cubes-clickhouse\/#Conclusion)\n  - [You might also like:](https:\/\/chistadata.com\/groupby-groupings-rollups-cubes-clickhouse\/#You_might_also_like)\n## Introduction\nGrouping, rollup, and cube are SQL query operations that allow for grouping and aggregation of data based on multiple dimensions or attributes. In ClickHouse, these operations are implemented using the GROUP BY clause, which allows you to group data based on one or more columns. Here are some real-life data examples to illustrate how to implement groupings, rollups, and cubes in ClickHouse:\n\n## Example 1: Sales Data\nSuppose we have a sales table with the following columns: order\\_id, customer\\_id, order\\_date, product\\_id, and quantity. We want to calculate the total quantity sold for each product and each month. Here’s how we can do this using grouping:\n```\nSELECT product_id, toMonth(order_date) AS month, sum(quantity) AS total_quantity\nFROM sales\nGROUP BY product_id, month\nORDER BY product_id, month\n```\nThis query will group the sales data by product\\_id and month, and calculate the total quantity sold for each combination of product and month. The toMonth() function is used to extract the month from the order\\_date column.\n\n## **Example 2: Web Traffic Data**\nSuppose we have a web traffic table with the following columns: timestamp, ip\\_address, page\\_url, user\\_agent. We want to calculate the number of page views by browser type and operating system. Here’s how we can do this using rollup:\n```\nSELECT\nCASE\nWHEN user_agent LIKE ‘%Firefox%’ THEN ‘Firefox’\nWHEN user_agent LIKE ‘%Chrome%’ THEN ‘Chrome’\nELSE ‘Other’\nEND AS browser,\nCASE\nWHEN user_agent LIKE ‘%Windows%’ THEN ‘Windows’\nWHEN user_agent LIKE ‘%Mac OS%’ THEN ‘Mac OS’\nELSE ‘Other’\nEND AS os,\ncount(*) AS page_views\nFROM web_traffic\nGROUP BY ROLLUP(browser, os)\nORDER BY browser, os\n```\nThis query will group the web traffic data by browser and operating system, and calculate the number of page views for each combination. The ROLLUP() function is used to create a hierarchy of subtotals, so the query will also return subtotals for each browser and for each operating system.\n\n## **Example 3: Employee Data**\nSuppose we have an employee table with the following columns: employee\\_id, department, job\\_title, salary. We want to calculate the average salary by department and job title, and also calculate subtotals by department and totals for all employees. Here’s how we can do this using cube:\n```\nSELECT department, job_title, avg(salary) AS avg_salary\nFROM employees\nGROUP BY CUBE(department, job_title)\nORDER BY department, job_title\n```\nThis query will group the employee data by department and job title, and calculate the average salary for each combination. The CUBE() function is used to create a hierarchy of subtotals and totals, so the query will also return subtotals by department and totals for all employees.\n\n## Conclusion\nIn summary, grouping, rollup, and cube are powerful SQL query operations that allow for grouping and aggregation of data based on multiple dimensions or attributes. In ClickHouse, these operations are implemented using the GROUP BY clause, along with functions such as ROLLUP() and CUBE(). By using these operations, you can gain deeper insights into your data and perform complex analysis on large-scale data sets.\n\nTo read more about GROUPBY & the EXPLAIN tool in ClickHouse, do consider reading the below articles\n\n- [ClickHouse Performance: Optimizing HASH GROUP BY and ORDER BY Queries](https:\/\/chistadata.com\/optimizing-hash-group-by-and-order-by-queries-in-clickhouse-strategies-for-enhanced-performance\/)\n- [ClickHouse EXPLAIN: Determine JOIN Order in Query Execution Plans](https:\/\/chistadata.com\/how-to-determine-the-join-order-in-a-clickhouse-execution-plan\/)\n- [Comprehensive Guide to ClickHouse EXPLAIN](https:\/\/chistadata.com\/analyse-queries-in-clickhouse\/)\n### You might also like:\n- [ClickHouse vs Snowflake: Choosing the Right Data Analytics Platform for Your Business](https:\/\/chistadata.com\/clickhouse-vs-snowflake-choose-right-data-analytics\/)\n- [Comprehensive Guide for ClickHouse Horizontal Scaling and Capacity Planning](https:\/\/chistadata.com\/clickhouse-horizontal-scaling-capacity-planning\/)\n- [Aggregate Functions via ClickHouse](https:\/\/chistadata.com\/aggregate-functions-via-clickhouse\/)\n- [Generative AI in Analytics: Part 1 – Unlocking New Possibilities with ClickHouse](https:\/\/chistadata.com\/generative-ai-analytics-with-clickhouse\/)\n- [How to Implement Partial Indexes in ClickHouse](https:\/\/chistadata.com\/how-to-implement-partial-indexes-in-clickhouse\/)","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

1 day 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 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://chistadata.com/groupby-groupings-rollups-cubes-clickhouse/
Last Crawled	2026-04-10 03:42:39 (1 day ago)
First Indexed	2024-03-05 20:29:10 (2 years ago)
HTTP Status Code	200
Meta Title	Using GROUPBY for Groupings, Rolllups and Cubes in ClickHouse
Meta Description	In this article, we explore how ClickHouse operations such as grouping, rollup, and cube are implemented using the GROUPBY clause
Meta Canonical	null
Boilerpipe Text	Table of Contents Introduction Example 1: Sales Data Example 2: Web Traffic Data Example 3: Employee Data Conclusion You might also like: Introduction Grouping, rollup, and cube are SQL query operations that allow for grouping and aggregation of data based on multiple dimensions or attributes. In ClickHouse, these operations are implemented using the GROUP BY clause, which allows you to group data based on one or more columns. Here are some real-life data examples to illustrate how to implement groupings, rollups, and cubes in ClickHouse: Example 1: Sales Data Suppose we have a sales table with the following columns: order_id, customer_id, order_date, product_id, and quantity. We want to calculate the total quantity sold for each product and each month. Here’s how we can do this using grouping: SELECT product_id, toMonth(order_date) AS month, sum(quantity) AS total_quantity FROM sales GROUP BY product_id, month ORDER BY product_id, month This query will group the sales data by product_id and month, and calculate the total quantity sold for each combination of product and month. The toMonth() function is used to extract the month from the order_date column. Example 2: Web Traffic Data Suppose we have a web traffic table with the following columns: timestamp, ip_address, page_url, user_agent. We want to calculate the number of page views by browser type and operating system. Here’s how we can do this using rollup: SELECT CASE WHEN user_agent LIKE ‘%Firefox%’ THEN ‘Firefox’ WHEN user_agent LIKE ‘%Chrome%’ THEN ‘Chrome’ ELSE ‘Other’ END AS browser, CASE WHEN user_agent LIKE ‘%Windows%’ THEN ‘Windows’ WHEN user_agent LIKE ‘%Mac OS%’ THEN ‘Mac OS’ ELSE ‘Other’ END AS os, count(*) AS page_views FROM web_traffic GROUP BY ROLLUP(browser, os) ORDER BY browser, os This query will group the web traffic data by browser and operating system, and calculate the number of page views for each combination. The ROLLUP() function is used to create a hierarchy of subtotals, so the query will also return subtotals for each browser and for each operating system. Example 3: Employee Data Suppose we have an employee table with the following columns: employee_id, department, job_title, salary. We want to calculate the average salary by department and job title, and also calculate subtotals by department and totals for all employees. Here’s how we can do this using cube: SELECT department, job_title, avg(salary) AS avg_salary FROM employees GROUP BY CUBE(department, job_title) ORDER BY department, job_title This query will group the employee data by department and job title, and calculate the average salary for each combination. The CUBE() function is used to create a hierarchy of subtotals and totals, so the query will also return subtotals by department and totals for all employees. Conclusion In summary, grouping, rollup, and cube are powerful SQL query operations that allow for grouping and aggregation of data based on multiple dimensions or attributes. In ClickHouse, these operations are implemented using the GROUP BY clause, along with functions such as ROLLUP() and CUBE(). By using these operations, you can gain deeper insights into your data and perform complex analysis on large-scale data sets. To read more about GROUPBY & the EXPLAIN tool in ClickHouse, do consider reading the below articles ClickHouse Performance: Optimizing HASH GROUP BY and ORDER BY Queries ClickHouse EXPLAIN: Determine JOIN Order in Query Execution Plans Comprehensive Guide to ClickHouse EXPLAIN You might also like: ClickHouse vs Snowflake: Choosing the Right Data Analytics Platform for Your Business Comprehensive Guide for ClickHouse Horizontal Scaling and Capacity Planning Aggregate Functions via ClickHouse Generative AI in Analytics: Part 1 – Unlocking New Possibilities with ClickHouse How to Implement Partial Indexes in ClickHouse
Markdown	[ChistaDATA Inc. Enterprise-class 24\7 ClickHouse Consultative Support and Managed Services](https://chistadata.com/ "ChistaDATA Inc.") - [ChistaDATA](https://chistadata.com/) - [ClickHouse®](https://chistadata.com/understanding-clickhouse/) - [MergeTree](https://chistadata.com/mergetree/) - [Why is ClickHouse So Fast](https://chistadata.com/why-clickhouse-is-so-fast-2/) - [Columnar Stores](https://chistadata.com/analyzing-performance-exploring-columnar-stores-vs-row-based-databases/) - [Vectorized Query](https://chistadata.com/clickhouses-vectorized-query-processing-unleashing-columnar-power/) - [For CTOs](https://chistadata.com/chistadata-cto/) - [Data Warehousing](https://chistadata.com/database-warehousing-support/) - [Engineering](https://chistadata.com/chistadata-server-for-clickhouse/) - [Real-Time Analytics](https://chistadata.com/realtimeanalytics-success-partnering-with-chistadata-to-build-optimal-scalable-and-reliable-solutions-on-clickhouse/) - [Break Fix Engineering](https://chistadata.com/chistadata-break-fix-services-clickhouse-support/) - [Data Archiving](https://chistadata.com/unlocking-the-power-of-clickhouse-from-real-time-analytics-to-reliable-archive-storage/) - [Cloud Native ClickHouse](https://chistadata.com/custom-data-fabric-solutions-data-fabric/) - [ClickHouse Consulting](https://chistadata.com/clickhouse-consulting/) - [Performance Audit](https://chistadata.com/clickhouse-performance-audit-for-performance/) - [Pre- Engagement Questionnaire](https://chistadata.com/clickhouse-performance-audit/) - [ClickHouse Strategy](https://chistadata.com/data-strategy-how-to-build-one/) - [Online Ticketing System](https://support.chistadata.com/) - [Support](https://chistadata.com/clickhouse-support/) - [ClickHouse Migration](https://chistadata.com/clickhouse-migration/) - [ClickHouse Audit](https://chistadata.com/clickhouse-performance-audit-recommendation/) - [Real-Time Analytics](https://chistadata.com/real-time-analytics-with-clickhouse/) - [Data Warehousing Support](https://chistadata.com/database-warehousing-support/) - [Data Analytics](https://chistadata.com/from-batch-processing-complexities-to-clickhouse-excellence-simplifying-data-analytics-with-chistadata/) - [ChistaDATA Analytics](https://chistadata.com/high-performance-real-time-analytics/) - [Gen AI](https://chistadata.com/chistadata-gen-ai-support-with-clickhouse/) - [Online Ticketing System](https://support.chistadata.com/) - [Managed Services](https://chistadata.com/clickhouse-services/) - [ClickHouse Services](https://chistadata.com/clickhouse-managed-services/) - [DBA Services](https://chistadata.com/fully-managed-clickhouse-services/) - [ClickHouse Performance](https://chistadata.com/clickhouse-performance-tuning-and-optimization/) - [Data Strategy](https://chistadata.com/crafting-right-data-strategy/) - [ClickHouse Analytics](https://chistadata.com/unlock-insights-chistadata-real-time-analytics/) - [Data Archiving](https://chistadata.com/rdbms-archival-chistadata-fabric/) - [DBaaS Optimization](https://chistadata.com/dbaas-optimisation/) - [Data SRE](https://chistadata.com/data-sre/) - [Online Ticketing System](https://support.chistadata.com/) - [Blog](https://chistadata.com/blog/) - [Shiv Iyer Talks](https://speakerdeck.com/shiviyer007) - [ChistaDATA Blog](https://chistadata.com/blog/) - [University](https://chistadata.com/chistadata-university/) - [Careers](https://chistadata.com/careers/) - [Contact](https://chistadata.com/contact-chistadata-inc/) - [Twitter](https://twitter.com/chistadata) - [Facebook](https://www.facebook.com/ChistaDATAOfficial/) - [LinkedIn](https://www.linkedin.com/company/chistadata) - [Shiv Iyer](https://www.linkedin.com/in/thewebscaledba/recent-activity/articles/) - [GitHub](https://github.com/ChistaDATA) - [@ShivIyer](https://github.com/shiviyer/Blogs/wiki) [Home](https://chistadata.com/ "Home") [ClickHouse](https://chistadata.com/category/clickhouse/ "ClickHouse") Using GROUPBY for Groupings, Rollups and Cubes in ClickHouse # Using GROUPBY for Groupings, Rollups and Cubes in ClickHouse [Shiv Iyer](https://chistadata.com/author/chistadata/) [ClickHouse](https://chistadata.com/category/clickhouse/), [ChistaDATA](https://chistadata.com/category/chistadata/), [ClickHouse DBA Script](https://chistadata.com/category/clickhouse-dba-script/), [ClickHouse DBA Support](https://chistadata.com/category/clickhouse-dba-support/), [ClickHouse Internals](https://chistadata.com/category/clickhouse-internals/), [ClickHouse Performance](https://chistadata.com/category/clickhouse-performance/), [ClickHouse Reliability](https://chistadata.com/category/clickhouse-reliability/) Table of Contents [Toggle](https://chistadata.com/groupby-groupings-rollups-cubes-clickhouse/) - [Introduction](https://chistadata.com/groupby-groupings-rollups-cubes-clickhouse/#Introduction) - [Example 1: Sales Data](https://chistadata.com/groupby-groupings-rollups-cubes-clickhouse/#Example_1_Sales_Data) - [Example 2: Web Traffic Data](https://chistadata.com/groupby-groupings-rollups-cubes-clickhouse/#Example_2_Web_Traffic_Data) - [Example 3: Employee Data](https://chistadata.com/groupby-groupings-rollups-cubes-clickhouse/#Example_3_Employee_Data) - [Conclusion](https://chistadata.com/groupby-groupings-rollups-cubes-clickhouse/#Conclusion) - [You might also like:](https://chistadata.com/groupby-groupings-rollups-cubes-clickhouse/#You_might_also_like) ## Introduction Grouping, rollup, and cube are SQL query operations that allow for grouping and aggregation of data based on multiple dimensions or attributes. In ClickHouse, these operations are implemented using the GROUP BY clause, which allows you to group data based on one or more columns. Here are some real-life data examples to illustrate how to implement groupings, rollups, and cubes in ClickHouse: ## Example 1: Sales Data Suppose we have a sales table with the following columns: order\_id, customer\_id, order\_date, product\_id, and quantity. We want to calculate the total quantity sold for each product and each month. Here’s how we can do this using grouping: ``` SELECT product_id, toMonth(order_date) AS month, sum(quantity) AS total_quantity FROM sales GROUP BY product_id, month ORDER BY product_id, month ``` This query will group the sales data by product\_id and month, and calculate the total quantity sold for each combination of product and month. The toMonth() function is used to extract the month from the order\_date column. ## Example 2: Web Traffic Data* Suppose we have a web traffic table with the following columns: timestamp, ip\_address, page\_url, user\_agent. We want to calculate the number of page views by browser type and operating system. Here’s how we can do this using rollup: ``` SELECT CASE WHEN user_agent LIKE ‘%Firefox%’ THEN ‘Firefox’ WHEN user_agent LIKE ‘%Chrome%’ THEN ‘Chrome’ ELSE ‘Other’ END AS browser, CASE WHEN user_agent LIKE ‘%Windows%’ THEN ‘Windows’ WHEN user_agent LIKE ‘%Mac OS%’ THEN ‘Mac OS’ ELSE ‘Other’ END AS os, count() AS page_views FROM web_traffic GROUP BY ROLLUP(browser, os) ORDER BY browser, os ``` This query will group the web traffic data by browser and operating system, and calculate the number of page views for each combination. The ROLLUP() function is used to create a hierarchy of subtotals, so the query will also return subtotals for each browser and for each operating system. ## Example 3: Employee Data* Suppose we have an employee table with the following columns: employee\_id, department, job\_title, salary. We want to calculate the average salary by department and job title, and also calculate subtotals by department and totals for all employees. Here’s how we can do this using cube: ``` SELECT department, job_title, avg(salary) AS avg_salary FROM employees GROUP BY CUBE(department, job_title) ORDER BY department, job_title ``` This query will group the employee data by department and job title, and calculate the average salary for each combination. The CUBE() function is used to create a hierarchy of subtotals and totals, so the query will also return subtotals by department and totals for all employees. ## Conclusion In summary, grouping, rollup, and cube are powerful SQL query operations that allow for grouping and aggregation of data based on multiple dimensions or attributes. In ClickHouse, these operations are implemented using the GROUP BY clause, along with functions such as ROLLUP() and CUBE(). By using these operations, you can gain deeper insights into your data and perform complex analysis on large-scale data sets. To read more about GROUPBY & the EXPLAIN tool in ClickHouse, do consider reading the below articles - [ClickHouse Performance: Optimizing HASH GROUP BY and ORDER BY Queries](https://chistadata.com/optimizing-hash-group-by-and-order-by-queries-in-clickhouse-strategies-for-enhanced-performance/) - [ClickHouse EXPLAIN: Determine JOIN Order in Query Execution Plans](https://chistadata.com/how-to-determine-the-join-order-in-a-clickhouse-execution-plan/) - [Comprehensive Guide to ClickHouse EXPLAIN](https://chistadata.com/analyse-queries-in-clickhouse/) ### You might also like: - [ClickHouse vs Snowflake: Choosing the Right Data Analytics Platform for Your Business](https://chistadata.com/clickhouse-vs-snowflake-choose-right-data-analytics/) - [Comprehensive Guide for ClickHouse Horizontal Scaling and Capacity Planning](https://chistadata.com/clickhouse-horizontal-scaling-capacity-planning/) - [Aggregate Functions via ClickHouse](https://chistadata.com/aggregate-functions-via-clickhouse/) - [Generative AI in Analytics: Part 1 – Unlocking New Possibilities with ClickHouse](https://chistadata.com/generative-ai-analytics-with-clickhouse/) - [How to Implement Partial Indexes in ClickHouse](https://chistadata.com/how-to-implement-partial-indexes-in-clickhouse/) ![](https://secure.gravatar.com/avatar/6becbb82f32acaaa1c0d6efde7a3863d0d17d5dd0ef64be2421b64d88273c7c0?s=125&d=mm&r=g) About Shiv Iyer [271 Articles](https://chistadata.com/author/chistadata/ "More articles written by Shiv Iyer'") Open Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally. [Website](https://chistadata.com%20 "Visit the website of Shiv Iyer") [Facebook](https://www.facebook.com/ChistaDATAOfficial/ "Follow Shiv Iyer on Facebook") [Twitter](https://twitter.com/thewebscaledba "Follow Shiv Iyer on Twitter") [LinkedIn](https://www.linkedin.com/in/thewebscaledba/ "Follow Shiv Iyer on LinkedIn") [![Comprehensive Guide to ChistaDATA's ClickHouse Performance Audit](https://chistadata.com/wp-content/uploads/2023/05/Comprehensive-Guide-to-ChistaDATAs-ClickHouse-Performance-Audit--80x60.jpeg)PreviousComprehensive Guide to ChistaDATA’s ClickHouse Performance Audit](https://chistadata.com/chistadata-clickhouse-performance-audit-guide/) [![Implementing Parallel Replicas with Dynamic Shards](https://chistadata.com/wp-content/uploads/2023/05/shutterstock_155390825-80x60.jpg)NextClickHouse Horizontal Scaling: Implementing Parallel Replicas with Dynamic Shards](https://chistadata.com/parallel-replicas-with-dynamic-shards-horizontal-scaling-clickhouse/) #### ChistaDATA is committed to open source software and building high performance ColumnStores In the spirit of freedom, independence and innovation. ChistaDATA Corporation* is not affiliated with ClickHouse Corporation* #### Tell us how we can help\! #### Search ChistaDATA Website #### ★READ THIS WARNING★ *\ Everything changes over time –** Our blogs/posts and comments changes over time, That’s how it should be! Whatever we comment from ChistaDATA Inc. Teams (including Shiv Iyer) and other stakeholders or guest bloggers posted here are never permanent, These things worked for us. But, there is no guarantee they will work for you too, When using the recommendations from ChistaDATA or MinervaDB or MinervaSQL or any other online resources / Google, You must test the advice before applying them to your production systems, and always invest for a robust Database DR solution, Thank you for understanding. #### Recent Posts from ChistaDATA - [Advanced eBPF-Based Performance Analysis for ClickHouse](https://chistadata.com/ebpf-performance-analysis-clickhouse/) - [ClickHouse Performance Detective’s Toolkit](https://chistadata.com/clickhouse-performance-toolkit/) - [How MergeTree Internals Drive Query Latency](https://chistadata.com/mergetree-internals-query-latency/) - [ClickHouse Thread Contention and Troubleshooting](https://chistadata.com/clickhouse-thread-contention-and-troubleshooting/) - [Designing ClickHouse for Mixed Workloads](https://chistadata.com/designing-clickhouse-for-mixed-workloads/) #### ☎ TOLL FREE PHONE (24\7) ### (844)395-5717* #### 🚩 ChistaDATA Inc. FAX ### \+1 (209) 314-2364 #### CORPORATE ADDRESS: CALIFORNIA ChistaDATA Inc. 440 N BARRANCA AVE \#9718 COVINA, CA 91723 #### CORPORATE ADDRESS: NEW CASTLE, DELAWARE ChistaDATA Inc., 256 Chapman Road STE 105-4, Newark, New Castle 19702, Delaware #### CORPORATE ADDRESS: DELAWARE ChistaDATA Inc., PO Box 2093 PHILADELPHIA PIKE \#3339 CLAYMONT, DE 19703 #### HOW CAN WE HELP? We are committed to building Optimal, Scalable, Highly Available, Reliable, Fault-Tolerant and Secured Database Infrastructure Operations for WebScale to our customers globally #### CHISTADATA IS COMMITTED TO OPEN SOURCE SOFTWARE AND BUILDING HIGH PERFORMANCE COLUMNSTORES In the spirit of freedom, independence and innovation. ChistaDATA Corporation* is not affiliated with ClickHouse Corporation* #### ChistaDATA Inc. Knowledge base is licensed under the Apache License, Version 2.0 (the “License”) Copyright 2022 ChistaDATA Inc Licensed under the Apache License, Version 2.0 (the “License”); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an “AS IS” BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License. PostgreSQL is a registered trademark of the PostgreSQL Community Association. ClickHouse is a registered trademark of ClickHouse, Inc. MongoDB is a registered trademark of MongoDB, Inc. Couchbase is a registered trademark of Couchbase, Inc. Redis is a registered trademark of Redis Ltd. Apache Cassandra is a registered trademark of the Apache Software Foundation. Milvus is a registered trademark of Zilliz. MinIO is a registered trademark of MinIO, Inc. Amazon Redshift and Amazon Aurora are registered trademarks of \[Amazon.com\](http://amazon.com/), Inc. Google Cloud is a registered trademark of Google LLC. Snowflake is a registered trademark of Snowflake Inc. Databricks is a registered trademark of Databricks, Inc. MySQL and InnoDB are registered trademarks of Oracle Corporation. MariaDB is a trademark of MariaDB Corporation Ab. All other trademarks are the property of their respective owners. Any other product or company names mentioned may be trademarks or trade names of their respective owners. Copyright © 2010–2026. All Rights Reserved by ChistaDATA®. Table of Contents [×](https://chistadata.com/groupby-groupings-rollups-cubes-clickhouse/) - [Introduction](https://chistadata.com/groupby-groupings-rollups-cubes-clickhouse/#Introduction) - [Example 1: Sales Data](https://chistadata.com/groupby-groupings-rollups-cubes-clickhouse/#Example_1_Sales_Data) - [Example 2: Web Traffic Data](https://chistadata.com/groupby-groupings-rollups-cubes-clickhouse/#Example_2_Web_Traffic_Data) - [Example 3: Employee Data](https://chistadata.com/groupby-groupings-rollups-cubes-clickhouse/#Example_3_Employee_Data) - [Conclusion](https://chistadata.com/groupby-groupings-rollups-cubes-clickhouse/#Conclusion) - [You might also like:](https://chistadata.com/groupby-groupings-rollups-cubes-clickhouse/#You_might_also_like) [→ Index](https://chistadata.com/groupby-groupings-rollups-cubes-clickhouse/)
Readable Markdown	Table of Contents - [Introduction](https://chistadata.com/groupby-groupings-rollups-cubes-clickhouse/#Introduction) - [Example 1: Sales Data](https://chistadata.com/groupby-groupings-rollups-cubes-clickhouse/#Example_1_Sales_Data) - [Example 2: Web Traffic Data](https://chistadata.com/groupby-groupings-rollups-cubes-clickhouse/#Example_2_Web_Traffic_Data) - [Example 3: Employee Data](https://chistadata.com/groupby-groupings-rollups-cubes-clickhouse/#Example_3_Employee_Data) - [Conclusion](https://chistadata.com/groupby-groupings-rollups-cubes-clickhouse/#Conclusion) - [You might also like:](https://chistadata.com/groupby-groupings-rollups-cubes-clickhouse/#You_might_also_like) ## Introduction Grouping, rollup, and cube are SQL query operations that allow for grouping and aggregation of data based on multiple dimensions or attributes. In ClickHouse, these operations are implemented using the GROUP BY clause, which allows you to group data based on one or more columns. Here are some real-life data examples to illustrate how to implement groupings, rollups, and cubes in ClickHouse: ## Example 1: Sales Data Suppose we have a sales table with the following columns: order\_id, customer\_id, order\_date, product\_id, and quantity. We want to calculate the total quantity sold for each product and each month. Here’s how we can do this using grouping: ``` SELECT product_id, toMonth(order_date) AS month, sum(quantity) AS total_quantity FROM sales GROUP BY product_id, month ORDER BY product_id, month ``` This query will group the sales data by product\_id and month, and calculate the total quantity sold for each combination of product and month. The toMonth() function is used to extract the month from the order\_date column. ## Example 2: Web Traffic Data Suppose we have a web traffic table with the following columns: timestamp, ip\_address, page\_url, user\_agent. We want to calculate the number of page views by browser type and operating system. Here’s how we can do this using rollup: ``` SELECT CASE WHEN user_agent LIKE ‘%Firefox%’ THEN ‘Firefox’ WHEN user_agent LIKE ‘%Chrome%’ THEN ‘Chrome’ ELSE ‘Other’ END AS browser, CASE WHEN user_agent LIKE ‘%Windows%’ THEN ‘Windows’ WHEN user_agent LIKE ‘%Mac OS%’ THEN ‘Mac OS’ ELSE ‘Other’ END AS os, count() AS page_views FROM web_traffic GROUP BY ROLLUP(browser, os) ORDER BY browser, os ``` This query will group the web traffic data by browser and operating system, and calculate the number of page views for each combination. The ROLLUP() function is used to create a hierarchy of subtotals, so the query will also return subtotals for each browser and for each operating system. ## Example 3: Employee Data* Suppose we have an employee table with the following columns: employee\_id, department, job\_title, salary. We want to calculate the average salary by department and job title, and also calculate subtotals by department and totals for all employees. Here’s how we can do this using cube: ``` SELECT department, job_title, avg(salary) AS avg_salary FROM employees GROUP BY CUBE(department, job_title) ORDER BY department, job_title ``` This query will group the employee data by department and job title, and calculate the average salary for each combination. The CUBE() function is used to create a hierarchy of subtotals and totals, so the query will also return subtotals by department and totals for all employees. ## Conclusion In summary, grouping, rollup, and cube are powerful SQL query operations that allow for grouping and aggregation of data based on multiple dimensions or attributes. In ClickHouse, these operations are implemented using the GROUP BY clause, along with functions such as ROLLUP() and CUBE(). By using these operations, you can gain deeper insights into your data and perform complex analysis on large-scale data sets. To read more about GROUPBY & the EXPLAIN tool in ClickHouse, do consider reading the below articles - [ClickHouse Performance: Optimizing HASH GROUP BY and ORDER BY Queries](https://chistadata.com/optimizing-hash-group-by-and-order-by-queries-in-clickhouse-strategies-for-enhanced-performance/) - [ClickHouse EXPLAIN: Determine JOIN Order in Query Execution Plans](https://chistadata.com/how-to-determine-the-join-order-in-a-clickhouse-execution-plan/) - [Comprehensive Guide to ClickHouse EXPLAIN](https://chistadata.com/analyse-queries-in-clickhouse/) ### You might also like: - [ClickHouse vs Snowflake: Choosing the Right Data Analytics Platform for Your Business](https://chistadata.com/clickhouse-vs-snowflake-choose-right-data-analytics/) - [Comprehensive Guide for ClickHouse Horizontal Scaling and Capacity Planning](https://chistadata.com/clickhouse-horizontal-scaling-capacity-planning/) - [Aggregate Functions via ClickHouse](https://chistadata.com/aggregate-functions-via-clickhouse/) - [Generative AI in Analytics: Part 1 – Unlocking New Possibilities with ClickHouse](https://chistadata.com/generative-ai-analytics-with-clickhouse/) - [How to Implement Partial Indexes in ClickHouse](https://chistadata.com/how-to-implement-partial-indexes-in-clickhouse/)
Shard	166 (laksa)
Root Hash	8420801914178628366
Unparsed URL	com,chistadata!/groupby-groupings-rollups-cubes-clickhouse/ s443