🕷️ Crawler Inspector

URL Lookup

Direct Parameter Lookup

Raw Queries and Responses

1. Shard Calculation

Query:

Response:

Calculated Shard: 89 (from laksa128)

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/combinators\')), getAhrefsUnparsedNoserviceFromURL(\'https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators\'))) FORMAT JSONEachRow'

Response:

{"found_url":"https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators","crawl_time":1775290766,"first_indexed_time":1740099387,"http_code":200,"src_unparsed":"com,clickhouse!\/docs\/sql-reference\/aggregate-functions\/combinators s443","src_root_hash":"12633450985039531489","history_drop_reason":null,"meta_title":"Aggregate Function Combinators | ClickHouse Docs","meta_descriptions":["Documentation for Aggregate Function Combinators"],"attrs_boilerpipe_text":"The name of an aggregate function can have a suffix appended to it. This changes the way the aggregate function works.\n-If\n\nThe suffix -If can be appended to the name of any aggregate function. In this case, the aggregate function accepts an extra argument – a condition (Uint8 type). The aggregate function processes only the rows that trigger the condition. If the condition was not triggered even once, it returns a default value (usually zeros or empty strings).\nExamples:\nsumIf(column, cond)\n,\ncountIf(cond)\n,\navgIf(x, cond)\n,\nquantilesTimingIf(level1, level2)(x, cond)\n,\nargMinIf(arg, val, cond)\nand so on.\nWith conditional aggregate functions, you can calculate aggregates for several conditions at once, without using subqueries and\nJOIN\ns. For example, conditional aggregate functions can be used to implement the segment comparison functionality.\n-Array\n\nThe -Array suffix can be appended to any aggregate function. In this case, the aggregate function takes arguments of the 'Array(T)' type (arrays) instead of 'T' type arguments. If the aggregate function accepts multiple arguments, this must be arrays of equal lengths. When processing arrays, the aggregate function works like the original aggregate function across all array elements.\nExample 1:\nsumArray(arr)\n- Totals all the elements of all 'arr' arrays. In this example, it could have been written more simply:\nsum(arraySum(arr))\n.\nExample 2:\nuniqArray(arr)\n– Counts the number of unique elements in all 'arr' arrays. This could be done an easier way:\nuniq(arrayJoin(arr))\n, but it's not always possible to add 'arrayJoin' to a query.\n-If and -Array can be combined. However, 'Array' must come first, then 'If'. Examples:\nuniqArrayIf(arr, cond)\n,\nquantilesTimingArrayIf(level1, level2)(arr, cond)\n. Due to this order, the 'cond' argument won't be an array.\n-Map\n\nThe -Map suffix can be appended to any aggregate function. This will create an aggregate function which gets Map type as an argument, and aggregates values of each key of the map separately using the specified aggregate function. The result is also of a Map type.\nExample\nCREATE\nTABLE\nmap_map\n(\ndate\nDate\n,\ntimeslot\nDateTime\n,\nstatus\nMap\n(\nString\n,\nUInt64\n)\n)\nENGINE\n=\nMergeTree\nORDER\nBY\n(\n)\n;\nINSERT\nINTO\nmap_map\nVALUES\n(\n'2000-01-01'\n,\n'2000-01-01 00:00:00'\n,\n(\n[\n'a'\n,\n'b'\n,\n'c'\n]\n,\n[\n10\n,\n10\n,\n10\n]\n)\n)\n,\n(\n'2000-01-01'\n,\n'2000-01-01 00:00:00'\n,\n(\n[\n'c'\n,\n'd'\n,\n'e'\n]\n,\n[\n10\n,\n10\n,\n10\n]\n)\n)\n,\n(\n'2000-01-01'\n,\n'2000-01-01 00:01:00'\n,\n(\n[\n'd'\n,\n'e'\n,\n'f'\n]\n,\n[\n10\n,\n10\n,\n10\n]\n)\n)\n,\n(\n'2000-01-01'\n,\n'2000-01-01 00:01:00'\n,\n(\n[\n'f'\n,\n'g'\n,\n'g'\n]\n,\n[\n10\n,\n10\n,\n10\n]\n)\n)\n;\nSELECT\ntimeslot\n,\nsumMap\n(\nstatus\n)\n,\navgMap\n(\nstatus\n)\n,\nminMap\n(\nstatus\n)\nFROM\nmap_map\nGROUP\nBY\ntimeslot\n;\n┌────────────timeslot─┬─sumMap\n(\nstatus\n)\n───────────────────────┬─avgMap\n(\nstatus\n)\n───────────────────────┬─minMap\n(\nstatus\n)\n───────────────────────┐\n│\n2000\n-\n01\n-\n01\n00\n:\n00\n:\n00\n│ {\n'a'\n:\n10\n,\n'b'\n:\n10\n,\n'c'\n:\n20\n,\n'd'\n:\n10\n,\n'e'\n:\n10\n} │ {\n'a'\n:\n10\n,\n'b'\n:\n10\n,\n'c'\n:\n10\n,\n'd'\n:\n10\n,\n'e'\n:\n10\n} │ {\n'a'\n:\n10\n,\n'b'\n:\n10\n,\n'c'\n:\n10\n,\n'd'\n:\n10\n,\n'e'\n:\n10\n} │\n│\n2000\n-\n01\n-\n01\n00\n:\n01\n:\n00\n│ {\n'd'\n:\n10\n,\n'e'\n:\n10\n,\n'f'\n:\n20\n,\n'g'\n:\n20\n}        │ {\n'd'\n:\n10\n,\n'e'\n:\n10\n,\n'f'\n:\n10\n,\n'g'\n:\n10\n}        │ {\n'd'\n:\n10\n,\n'e'\n:\n10\n,\n'f'\n:\n10\n,\n'g'\n:\n10\n}        │\n└─────────────────────┴──────────────────────────────────────┴──────────────────────────────────────┴──────────────────────────────────────┘\n-SimpleState\n\nIf you apply this combinator, the aggregate function returns the same value but with a different type. This is a\nSimpleAggregateFunction(...)\nthat can be stored in a table to work with\nAggregatingMergeTree\ntables.\nSyntax\n<\naggFunction\n>\nSimpleState\n(\nx\n)\nArguments\nx\n— Aggregate function parameters.\nReturned values\nThe value of an aggregate function with the\nSimpleAggregateFunction(...)\ntype.\nExample\nQuery:\nWITH\nanySimpleState\n(\nnumber\n)\nAS\nc\nSELECT\ntoTypeName\n(\nc\n)\n,\nc\nFROM\nnumbers\n(\n1\n)\n;\nResult:\n┌─toTypeName(c)────────────────────────┬─c─┐\n│ SimpleAggregateFunction(any, UInt64) │ 0 │\n└──────────────────────────────────────┴───┘\n-State\n\nIf you apply this combinator, the aggregate function does not return the resulting value (such as the number of unique values for the\nuniq\nfunction), but an intermediate state of the aggregation (for\nuniq\n, this is the hash table for calculating the number of unique values). This is an\nAggregateFunction(...)\nthat can be used for further processing or stored in a table to finish aggregating later.\nNote\nPlease notice, that -MapState is not an invariant for the same data due to the fact that order of data in intermediate state can change, though it doesn't impact ingestion of this data.\nTo work with these states, use:\nAggregatingMergeTree\ntable engine.\nfinalizeAggregation\nfunction.\nrunningAccumulate\nfunction.\n-Merge\ncombinator.\n-MergeState\ncombinator.\n-Merge\n\nIf you apply this combinator, the aggregate function takes the intermediate aggregation state as an argument, combines the states to finish aggregation, and returns the resulting value.\n-MergeState\n\nMerges the intermediate aggregation states in the same way as the -Merge combinator. However, it does not return the resulting value, but an intermediate aggregation state, similar to the -State combinator.\n-ForEach\n\nConverts an aggregate function for tables into an aggregate function for arrays that aggregates the corresponding array items and returns an array of results. For example,\nsumForEach\nfor the arrays\n[1, 2]\n,\n[3, 4, 5]\nand\n[6, 7]\nreturns the result\n[10, 13, 5]\nafter adding together the corresponding array items.\n-Distinct\n\nEvery unique combination of arguments will be aggregated only once. Repeating values are ignored.\nExamples:\nsum(DISTINCT x)\n(or\nsumDistinct(x)\n),\ngroupArray(DISTINCT x)\n(or\ngroupArrayDistinct(x)\n),\ncorrStable(DISTINCT x, y)\n(or\ncorrStableDistinct(x, y)\n) and so on.\n-OrDefault\n\nChanges behavior of an aggregate function.\nIf an aggregate function does not have input values, with this combinator it returns the default value for its return data type. Applies to the aggregate functions that can take empty input data.\n-OrDefault\ncan be used with other combinators.\nSyntax\n<\naggFunction\n>\nOrDefault\n(\nx\n)\nArguments\nx\n— Aggregate function parameters.\nReturned values\nReturns the default value of an aggregate function's return type if there is nothing to aggregate.\nType depends on the aggregate function used.\nExample\nQuery:\nSELECT\navg\n(\nnumber\n)\n,\navgOrDefault\n(\nnumber\n)\nFROM\nnumbers\n(\n0\n)\nResult:\n┌─avg(number)─┬─avgOrDefault(number)─┐\n│         nan │                    0 │\n└─────────────┴──────────────────────┘\nAlso\n-OrDefault\ncan be used with another combinators. It is useful when the aggregate function does not accept the empty input.\nQuery:\nSELECT\navgOrDefaultIf\n(\nx\n,\nx\n>\n10\n)\nFROM\n(\nSELECT\ntoDecimal32\n(\n1.23\n,\n2\n)\nAS\nx\n)\nResult:\n┌─avgOrDefaultIf(x, greater(x, 10))─┐\n│                              0.00 │\n└───────────────────────────────────┘\n-OrNull\n\nChanges behavior of an aggregate function.\nThis combinator converts a result of an aggregate function to the\nNullable\ndata type. If the aggregate function does not have values to calculate it returns\nNULL\n.\n-OrNull\ncan be used with other combinators.\nSyntax\n<\naggFunction\n>\nOrNull\n(\nx\n)\nArguments\nx\n— Aggregate function parameters.\nReturned values\nThe result of the aggregate function, converted to the\nNullable\ndata type.\nNULL\n, if there is nothing to aggregate.\nType:\nNullable(aggregate function return type)\n.\nExample\nAdd\n-orNull\nto the end of aggregate function.\nQuery:\nSELECT\nsumOrNull\n(\nnumber\n)\n,\ntoTypeName\n(\nsumOrNull\n(\nnumber\n)\n)\nFROM\nnumbers\n(\n10\n)\nWHERE\nnumber\n>\n10\nResult:\n┌─sumOrNull(number)─┬─toTypeName(sumOrNull(number))─┐\n│              ᴺᵁᴸᴸ │ Nullable(UInt64)              │\n└───────────────────┴───────────────────────────────┘\nAlso\n-OrNull\ncan be used with another combinators. It is useful when the aggregate function does not accept the empty input.\nQuery:\nSELECT\navgOrNullIf\n(\nx\n,\nx\n>\n10\n)\nFROM\n(\nSELECT\ntoDecimal32\n(\n1.23\n,\n2\n)\nAS\nx\n)\nResult:\n┌─avgOrNullIf(x, greater(x, 10))─┐\n│                           ᴺᵁᴸᴸ │\n└────────────────────────────────┘\n-Resample\n\nLets you divide data into groups, and then separately aggregates the data in those groups. Groups are created by splitting the values from one column into intervals.\n<\naggFunction\n>\nResample\n(\nstart\n,\nend\n,\nstep\n)\n(\n<\naggFunction_params\n>\n,\nresampling_key\n)\nArguments\nstart\n— Starting value of the whole required interval for\nresampling_key\nvalues.\nstop\n— Ending value of the whole required interval for\nresampling_key\nvalues. The whole interval does not include the\nstop\nvalue\n[start, stop)\n.\nstep\n— Step for separating the whole interval into subintervals. The\naggFunction\nis executed over each of those subintervals independently.\nresampling_key\n— Column whose values are used for separating data into intervals.\naggFunction_params\n—\naggFunction\nparameters.\nReturned values\nArray of\naggFunction\nresults for each subinterval.\nExample\nConsider the\npeople\ntable with the following data:\n┌─name───┬─age─┬─wage─┐\n│ John   │  16 │   10 │\n│ Alice  │  30 │   15 │\n│ Mary   │  35 │    8 │\n│ Evelyn │  48 │ 11.5 │\n│ David  │  62 │  9.9 │\n│ Brian  │  60 │   16 │\n└────────┴─────┴──────┘\nLet's get the names of the people whose age lies in the intervals of\n[30,60)\nand\n[60,75)\n. Since we use integer representation for age, we get ages in the\n[30, 59]\nand\n[60,74]\nintervals.\nTo aggregate names in an array, we use the\ngroupArray\naggregate function. It takes one argument. In our case, it's the\nname\ncolumn. The\ngroupArrayResample\nfunction should use the\nage\ncolumn to aggregate names by age. To define the required intervals, we pass the\n30, 75, 30\narguments into the\ngroupArrayResample\nfunction.\nSELECT\ngroupArrayResample\n(\n30\n,\n75\n,\n30\n)\n(\nname\n,\nage\n)\nFROM\npeople\n┌─groupArrayResample(30, 75, 30)(name, age)─────┐\n│ [['Alice','Mary','Evelyn'],['David','Brian']] │\n└───────────────────────────────────────────────┘\nConsider the results.\nJohn\nis out of the sample because he's too young. Other people are distributed according to the specified age intervals.\nNow let's count the total number of people and their average wage in the specified age intervals.\nSELECT\ncountResample\n(\n30\n,\n75\n,\n30\n)\n(\nname\n,\nage\n)\nAS\namount\n,\navgResample\n(\n30\n,\n75\n,\n30\n)\n(\nwage\n,\nage\n)\nAS\navg_wage\nFROM\npeople\n┌─amount─┬─avg_wage──────────────────┐\n│ [3,2]  │ [11.5,12.949999809265137] │\n└────────┴───────────────────────────┘\n-ArgMin\n\nThe suffix -ArgMin can be appended to the name of any aggregate function. In this case, the aggregate function accepts an additional argument, which should be any comparable expression. The aggregate function processes only the rows that have the minimum value for the specified extra expression.\nExamples:\nsumArgMin(column, expr)\n,\ncountArgMin(expr)\n,\navgArgMin(x, expr)\nand so on.\n-ArgMax\n\nSimilar to suffix -ArgMin but processes only the rows that have the maximum value for the specified extra expression.\nRelated Content\n\nBlog:\nUsing Aggregate Combinators in ClickHouse","attrs_markdown":"[Skip to main content](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#__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\/combinators)\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\/combinators)\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\/combinators)\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\/combinators)\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\\.7k](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=43f221c6-40d4-4576-931b-74d90e353b1e&pagePath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fcombinators&origPath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fcombinators&utm_ga=GA1.1.657831716.1775290767)\n\n[Get started](https:\/\/console.clickhouse.cloud\/signUp?loc=docs-nav-signUp-cta&glxid=43f221c6-40d4-4576-931b-74d90e353b1e&pagePath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fcombinators&origPath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fcombinators&utm_ga=GA1.1.657831716.1775290767)\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\/combinators)\n\n- [English](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators)\n- [日本語](https:\/\/clickhouse.com\/docs\/jp\/sql-reference\/aggregate-functions\/combinators)\n- [中文](https:\/\/clickhouse.com\/docs\/zh\/sql-reference\/aggregate-functions\/combinators)\n- [Русский](https:\/\/clickhouse.com\/docs\/ru\/sql-reference\/aggregate-functions\/combinators)\n- [한국어](https:\/\/clickhouse.com\/docs\/ko\/sql-reference\/aggregate-functions\/combinators)\n\n[Skip to main content](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#__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\/combinators)\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\/combinators)\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\/combinators)\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\/combinators)\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\\.7k](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=43f221c6-40d4-4576-931b-74d90e353b1e&pagePath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fcombinators&origPath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fcombinators&utm_ga=GA1.1.657831716.1775290767)\n\n[Get started](https:\/\/console.clickhouse.cloud\/signUp?loc=docs-nav-signUp-cta&glxid=43f221c6-40d4-4576-931b-74d90e353b1e&pagePath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fcombinators&origPath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fcombinators&utm_ga=GA1.1.657831716.1775290767)\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\/combinators)\n\n- [English](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators)\n- [日本語](https:\/\/clickhouse.com\/docs\/jp\/sql-reference\/aggregate-functions\/combinators)\n- [中文](https:\/\/clickhouse.com\/docs\/zh\/sql-reference\/aggregate-functions\/combinators)\n- [Русский](https:\/\/clickhouse.com\/docs\/ru\/sql-reference\/aggregate-functions\/combinators)\n- [한국어](https:\/\/clickhouse.com\/docs\/ko\/sql-reference\/aggregate-functions\/combinators)\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\/combinators)\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- Combinators\n\n[Edit this page](https:\/\/github.com\/ClickHouse\/ClickHouse\/tree\/master\/docs\/en\/sql-reference\/aggregate-functions\/combinators.md)\n\n# Aggregate function combinators\nThe name of an aggregate function can have a suffix appended to it. This changes the way the aggregate function works.\n\n## \\-If[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-if \"Direct link to -If\")\nThe suffix -If can be appended to the name of any aggregate function. In this case, the aggregate function accepts an extra argument – a condition (Uint8 type). The aggregate function processes only the rows that trigger the condition. If the condition was not triggered even once, it returns a default value (usually zeros or empty strings).\n\nExamples: `sumIf(column, cond)`, `countIf(cond)`, `avgIf(x, cond)`, `quantilesTimingIf(level1, level2)(x, cond)`, `argMinIf(arg, val, cond)` and so on.\n\nWith conditional aggregate functions, you can calculate aggregates for several conditions at once, without using subqueries and `JOIN`s. For example, conditional aggregate functions can be used to implement the segment comparison functionality.\n\n## \\-Array[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-array \"Direct link to -Array\")\nThe -Array suffix can be appended to any aggregate function. In this case, the aggregate function takes arguments of the 'Array(T)' type (arrays) instead of 'T' type arguments. If the aggregate function accepts multiple arguments, this must be arrays of equal lengths. When processing arrays, the aggregate function works like the original aggregate function across all array elements.\n\nExample 1: `sumArray(arr)` - Totals all the elements of all 'arr' arrays. In this example, it could have been written more simply: `sum(arraySum(arr))`.\n\nExample 2: `uniqArray(arr)` – Counts the number of unique elements in all 'arr' arrays. This could be done an easier way: `uniq(arrayJoin(arr))`, but it's not always possible to add 'arrayJoin' to a query.\n\n\\-If and -Array can be combined. However, 'Array' must come first, then 'If'. Examples: `uniqArrayIf(arr, cond)`, `quantilesTimingArrayIf(level1, level2)(arr, cond)`. Due to this order, the 'cond' argument won't be an array.\n\n## \\-Map[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-map \"Direct link to -Map\")\nThe -Map suffix can be appended to any aggregate function. This will create an aggregate function which gets Map type as an argument, and aggregates values of each key of the map separately using the specified aggregate function. The result is also of a Map type.\n\n**Example**\n```\nCREATE TABLE map_map(\n    date Date,\n    timeslot DateTime,\n    status Map(String, UInt64)\n) ENGINE = MergeTree\nORDER BY ();\n\nINSERT INTO map_map VALUES\n    ('2000-01-01', '2000-01-01 00:00:00', (['a', 'b', 'c'], [10, 10, 10])),\n    ('2000-01-01', '2000-01-01 00:00:00', (['c', 'd', 'e'], [10, 10, 10])),\n    ('2000-01-01', '2000-01-01 00:01:00', (['d', 'e', 'f'], [10, 10, 10])),\n    ('2000-01-01', '2000-01-01 00:01:00', (['f', 'g', 'g'], [10, 10, 10]));\n\nSELECT\n    timeslot,\n    sumMap(status),\n    avgMap(status),\n    minMap(status)\nFROM map_map\nGROUP BY timeslot;\n\n┌────────────timeslot─┬─sumMap(status)───────────────────────┬─avgMap(status)───────────────────────┬─minMap(status)───────────────────────┐\n│ 2000-01-01 00:00:00 │ {'a':10,'b':10,'c':20,'d':10,'e':10} │ {'a':10,'b':10,'c':10,'d':10,'e':10} │ {'a':10,'b':10,'c':10,'d':10,'e':10} │\n│ 2000-01-01 00:01:00 │ {'d':10,'e':10,'f':20,'g':20}        │ {'d':10,'e':10,'f':10,'g':10}        │ {'d':10,'e':10,'f':10,'g':10}        │\n└─────────────────────┴──────────────────────────────────────┴──────────────────────────────────────┴──────────────────────────────────────┘\n```\n## \\-SimpleState[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-simplestate \"Direct link to -SimpleState\")\nIf you apply this combinator, the aggregate function returns the same value but with a different type. This is a [SimpleAggregateFunction(...)](https:\/\/clickhouse.com\/docs\/sql-reference\/data-types\/simpleaggregatefunction) that can be stored in a table to work with [AggregatingMergeTree](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/aggregatingmergetree) tables.\n\n**Syntax**\n```\n<aggFunction>SimpleState(x)\n```\n**Arguments**\n\n- `x` — Aggregate function parameters.\n\n**Returned values**\n\nThe value of an aggregate function with the `SimpleAggregateFunction(...)` type.\n\n**Example**\n\nQuery:\n```\nWITH anySimpleState(number) AS c SELECT toTypeName(c), c FROM numbers(1);\n```\nResult:\n```\n┌─toTypeName(c)────────────────────────┬─c─┐\n│ SimpleAggregateFunction(any, UInt64) │ 0 │\n└──────────────────────────────────────┴───┘\n```\n## \\-State[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-state \"Direct link to -State\")\nIf you apply this combinator, the aggregate function does not return the resulting value (such as the number of unique values for the [uniq](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/reference\/uniq) function), but an intermediate state of the aggregation (for `uniq`, this is the hash table for calculating the number of unique values). This is an `AggregateFunction(...)` that can be used for further processing or stored in a table to finish aggregating later.\n\nNote\n\nPlease notice, that -MapState is not an invariant for the same data due to the fact that order of data in intermediate state can change, though it doesn't impact ingestion of this data.\n\nTo work with these states, use:\n\n- [AggregatingMergeTree](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/aggregatingmergetree) table engine.\n- [finalizeAggregation](https:\/\/clickhouse.com\/docs\/sql-reference\/functions\/other-functions#finalizeAggregation) function.\n- [runningAccumulate](https:\/\/clickhouse.com\/docs\/sql-reference\/functions\/other-functions#runningAccumulate) function.\n- [\\-Merge](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-merge) combinator.\n- [\\-MergeState](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-mergestate) combinator.\n## \\-Merge[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-merge \"Direct link to -Merge\")\nIf you apply this combinator, the aggregate function takes the intermediate aggregation state as an argument, combines the states to finish aggregation, and returns the resulting value.\n\n## \\-MergeState[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-mergestate \"Direct link to -MergeState\")\nMerges the intermediate aggregation states in the same way as the -Merge combinator. However, it does not return the resulting value, but an intermediate aggregation state, similar to the -State combinator.\n\n## \\-ForEach[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-foreach \"Direct link to -ForEach\")\nConverts an aggregate function for tables into an aggregate function for arrays that aggregates the corresponding array items and returns an array of results. For example, `sumForEach` for the arrays `[1, 2]`, `[3, 4, 5]`and`[6, 7]`returns the result `[10, 13, 5]` after adding together the corresponding array items.\n\n## \\-Distinct[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-distinct \"Direct link to -Distinct\")\nEvery unique combination of arguments will be aggregated only once. Repeating values are ignored. Examples: `sum(DISTINCT x)` (or `sumDistinct(x)`), `groupArray(DISTINCT x)` (or `groupArrayDistinct(x)`), `corrStable(DISTINCT x, y)` (or `corrStableDistinct(x, y)`) and so on.\n\n## \\-OrDefault[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-ordefault \"Direct link to -OrDefault\")\nChanges behavior of an aggregate function.\n\nIf an aggregate function does not have input values, with this combinator it returns the default value for its return data type. Applies to the aggregate functions that can take empty input data.\n\n`-OrDefault` can be used with other combinators.\n\n**Syntax**\n```\n<aggFunction>OrDefault(x)\n```\n**Arguments**\n\n- `x` — Aggregate function parameters.\n\n**Returned values**\n\nReturns the default value of an aggregate function's return type if there is nothing to aggregate.\n\nType depends on the aggregate function used.\n\n**Example**\n\nQuery:\n```\nSELECT avg(number), avgOrDefault(number) FROM numbers(0)\n```\nResult:\n```\n┌─avg(number)─┬─avgOrDefault(number)─┐\n│         nan │                    0 │\n└─────────────┴──────────────────────┘\n```\nAlso `-OrDefault` can be used with another combinators. It is useful when the aggregate function does not accept the empty input.\n\nQuery:\n```\nSELECT avgOrDefaultIf(x, x > 10)\nFROM\n(\n    SELECT toDecimal32(1.23, 2) AS x\n)\n```\nResult:\n```\n┌─avgOrDefaultIf(x, greater(x, 10))─┐\n│                              0.00 │\n└───────────────────────────────────┘\n```\n## \\-OrNull[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-ornull \"Direct link to -OrNull\")\nChanges behavior of an aggregate function.\n\nThis combinator converts a result of an aggregate function to the [Nullable](https:\/\/clickhouse.com\/docs\/sql-reference\/data-types\/nullable) data type. If the aggregate function does not have values to calculate it returns [NULL](https:\/\/clickhouse.com\/docs\/operations\/settings\/formats#input_format_null_as_default).\n\n`-OrNull` can be used with other combinators.\n\n**Syntax**\n```\n<aggFunction>OrNull(x)\n```\n**Arguments**\n\n- `x` — Aggregate function parameters.\n\n**Returned values**\n\n- The result of the aggregate function, converted to the `Nullable` data type.\n- `NULL`, if there is nothing to aggregate.\n\nType: `Nullable(aggregate function return type)`.\n\n**Example**\n\nAdd `-orNull` to the end of aggregate function.\n\nQuery:\n```\nSELECT sumOrNull(number), toTypeName(sumOrNull(number)) FROM numbers(10) WHERE number > 10\n```\nResult:\n```\n┌─sumOrNull(number)─┬─toTypeName(sumOrNull(number))─┐\n│              ᴺᵁᴸᴸ │ Nullable(UInt64)              │\n└───────────────────┴───────────────────────────────┘\n```\nAlso `-OrNull` can be used with another combinators. It is useful when the aggregate function does not accept the empty input.\n\nQuery:\n```\nSELECT avgOrNullIf(x, x > 10)\nFROM\n(\n    SELECT toDecimal32(1.23, 2) AS x\n)\n```\nResult:\n```\n┌─avgOrNullIf(x, greater(x, 10))─┐\n│                           ᴺᵁᴸᴸ │\n└────────────────────────────────┘\n```\n## \\-Resample[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-resample \"Direct link to -Resample\")\nLets you divide data into groups, and then separately aggregates the data in those groups. Groups are created by splitting the values from one column into intervals.\n```\n<aggFunction>Resample(start, end, step)(<aggFunction_params>, resampling_key)\n```\n**Arguments**\n\n- `start` — Starting value of the whole required interval for `resampling_key` values.\n- `stop` — Ending value of the whole required interval for `resampling_key` values. The whole interval does not include the `stop` value `[start, stop)`.\n- `step` — Step for separating the whole interval into subintervals. The `aggFunction` is executed over each of those subintervals independently.\n- `resampling_key` — Column whose values are used for separating data into intervals.\n- `aggFunction_params` — `aggFunction` parameters.\n\n**Returned values**\n\n- Array of `aggFunction` results for each subinterval.\n\n**Example**\n\nConsider the `people` table with the following data:\n```\n┌─name───┬─age─┬─wage─┐\n│ John   │  16 │   10 │\n│ Alice  │  30 │   15 │\n│ Mary   │  35 │    8 │\n│ Evelyn │  48 │ 11.5 │\n│ David  │  62 │  9.9 │\n│ Brian  │  60 │   16 │\n└────────┴─────┴──────┘\n```\nLet's get the names of the people whose age lies in the intervals of `[30,60)` and `[60,75)`. Since we use integer representation for age, we get ages in the `[30, 59]` and `[60,74]` intervals.\n\nTo aggregate names in an array, we use the [groupArray](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/reference\/grouparray) aggregate function. It takes one argument. In our case, it's the `name` column. The `groupArrayResample` function should use the `age` column to aggregate names by age. To define the required intervals, we pass the `30, 75, 30` arguments into the `groupArrayResample` function.\n```\nSELECT groupArrayResample(30, 75, 30)(name, age) FROM people\n```\n```\n┌─groupArrayResample(30, 75, 30)(name, age)─────┐\n│ [['Alice','Mary','Evelyn'],['David','Brian']] │\n└───────────────────────────────────────────────┘\n```\nConsider the results.\n\n`John` is out of the sample because he's too young. Other people are distributed according to the specified age intervals.\n\nNow let's count the total number of people and their average wage in the specified age intervals.\n```\nSELECT\n    countResample(30, 75, 30)(name, age) AS amount,\n    avgResample(30, 75, 30)(wage, age) AS avg_wage\nFROM people\n```\n```\n┌─amount─┬─avg_wage──────────────────┐\n│ [3,2]  │ [11.5,12.949999809265137] │\n└────────┴───────────────────────────┘\n```\n## \\-ArgMin[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-argmin \"Direct link to -ArgMin\")\nThe suffix -ArgMin can be appended to the name of any aggregate function. In this case, the aggregate function accepts an additional argument, which should be any comparable expression. The aggregate function processes only the rows that have the minimum value for the specified extra expression.\n\nExamples: `sumArgMin(column, expr)`, `countArgMin(expr)`, `avgArgMin(x, expr)` and so on.\n\n## \\-ArgMax[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-argmax \"Direct link to -ArgMax\")\nSimilar to suffix -ArgMin but processes only the rows that have the maximum value for the specified extra expression.\n\n## Related Content[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#related-content \"Direct link to Related Content\")\n- Blog: [Using Aggregate Combinators in ClickHouse](https:\/\/clickhouse.com\/blog\/aggregate-functions-combinators-in-clickhouse-for-arrays-maps-and-states)\n\n[Previous welchTTest](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/reference\/welchttest)\n\n[Next Parametric](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/parametric-functions)\n\n- [\\-If](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-if)\n- [\\-Array](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-array)\n- [\\-Map](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-map)\n- [\\-SimpleState](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-simplestate)\n- [\\-State](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-state)\n- [\\-Merge](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-merge)\n- [\\-MergeState](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-mergestate)\n- [\\-ForEach](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-foreach)\n- [\\-Distinct](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-distinct)\n- [\\-OrDefault](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-ordefault)\n- [\\-OrNull](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-ornull)\n- [\\-Resample](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-resample)\n- [\\-ArgMin](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-argmin)\n- [\\-ArgMax](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-argmax)\n- [Related Content](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#related-content)\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=43f221c6-40d4-4576-931b-74d90e353b1e&pagePath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fcombinators&origPath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fcombinators&utm_ga=GA1.1.657831716.1775290767)\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":"The name of an aggregate function can have a suffix appended to it. This changes the way the aggregate function works.\n\n## \\-If[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-if \"Direct link to -If\")\nThe suffix -If can be appended to the name of any aggregate function. In this case, the aggregate function accepts an extra argument – a condition (Uint8 type). The aggregate function processes only the rows that trigger the condition. If the condition was not triggered even once, it returns a default value (usually zeros or empty strings).\n\nExamples: `sumIf(column, cond)`, `countIf(cond)`, `avgIf(x, cond)`, `quantilesTimingIf(level1, level2)(x, cond)`, `argMinIf(arg, val, cond)` and so on.\n\nWith conditional aggregate functions, you can calculate aggregates for several conditions at once, without using subqueries and `JOIN`s. For example, conditional aggregate functions can be used to implement the segment comparison functionality.\n\n## \\-Array[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-array \"Direct link to -Array\")\nThe -Array suffix can be appended to any aggregate function. In this case, the aggregate function takes arguments of the 'Array(T)' type (arrays) instead of 'T' type arguments. If the aggregate function accepts multiple arguments, this must be arrays of equal lengths. When processing arrays, the aggregate function works like the original aggregate function across all array elements.\n\nExample 1: `sumArray(arr)` - Totals all the elements of all 'arr' arrays. In this example, it could have been written more simply: `sum(arraySum(arr))`.\n\nExample 2: `uniqArray(arr)` – Counts the number of unique elements in all 'arr' arrays. This could be done an easier way: `uniq(arrayJoin(arr))`, but it's not always possible to add 'arrayJoin' to a query.\n\n\\-If and -Array can be combined. However, 'Array' must come first, then 'If'. Examples: `uniqArrayIf(arr, cond)`, `quantilesTimingArrayIf(level1, level2)(arr, cond)`. Due to this order, the 'cond' argument won't be an array.\n\n## \\-Map[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-map \"Direct link to -Map\")\nThe -Map suffix can be appended to any aggregate function. This will create an aggregate function which gets Map type as an argument, and aggregates values of each key of the map separately using the specified aggregate function. The result is also of a Map type.\n\n**Example**\n```\nCREATE TABLE map_map(\n    date Date,\n    timeslot DateTime,\n    status Map(String, UInt64)\n) ENGINE = MergeTree\nORDER BY ();\n\nINSERT INTO map_map VALUES\n    ('2000-01-01', '2000-01-01 00:00:00', (['a', 'b', 'c'], [10, 10, 10])),\n    ('2000-01-01', '2000-01-01 00:00:00', (['c', 'd', 'e'], [10, 10, 10])),\n    ('2000-01-01', '2000-01-01 00:01:00', (['d', 'e', 'f'], [10, 10, 10])),\n    ('2000-01-01', '2000-01-01 00:01:00', (['f', 'g', 'g'], [10, 10, 10]));\n\nSELECT\n    timeslot,\n    sumMap(status),\n    avgMap(status),\n    minMap(status)\nFROM map_map\nGROUP BY timeslot;\n\n┌────────────timeslot─┬─sumMap(status)───────────────────────┬─avgMap(status)───────────────────────┬─minMap(status)───────────────────────┐\n│ 2000-01-01 00:00:00 │ {'a':10,'b':10,'c':20,'d':10,'e':10} │ {'a':10,'b':10,'c':10,'d':10,'e':10} │ {'a':10,'b':10,'c':10,'d':10,'e':10} │\n│ 2000-01-01 00:01:00 │ {'d':10,'e':10,'f':20,'g':20}        │ {'d':10,'e':10,'f':10,'g':10}        │ {'d':10,'e':10,'f':10,'g':10}        │\n└─────────────────────┴──────────────────────────────────────┴──────────────────────────────────────┴──────────────────────────────────────┘\n```\n## \\-SimpleState[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-simplestate \"Direct link to -SimpleState\")\nIf you apply this combinator, the aggregate function returns the same value but with a different type. This is a [SimpleAggregateFunction(...)](https:\/\/clickhouse.com\/docs\/sql-reference\/data-types\/simpleaggregatefunction) that can be stored in a table to work with [AggregatingMergeTree](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/aggregatingmergetree) tables.\n\n**Syntax**\n```\n<aggFunction>SimpleState(x)\n```\n**Arguments**\n\n- `x` — Aggregate function parameters.\n\n**Returned values**\n\nThe value of an aggregate function with the `SimpleAggregateFunction(...)` type.\n\n**Example**\n\nQuery:\n```\nWITH anySimpleState(number) AS c SELECT toTypeName(c), c FROM numbers(1);\n```\nResult:\n```\n┌─toTypeName(c)────────────────────────┬─c─┐\n│ SimpleAggregateFunction(any, UInt64) │ 0 │\n└──────────────────────────────────────┴───┘\n```\n## \\-State[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-state \"Direct link to -State\")\nIf you apply this combinator, the aggregate function does not return the resulting value (such as the number of unique values for the [uniq](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/reference\/uniq) function), but an intermediate state of the aggregation (for `uniq`, this is the hash table for calculating the number of unique values). This is an `AggregateFunction(...)` that can be used for further processing or stored in a table to finish aggregating later.\n\nNote\n\nPlease notice, that -MapState is not an invariant for the same data due to the fact that order of data in intermediate state can change, though it doesn't impact ingestion of this data.\n\nTo work with these states, use:\n\n- [AggregatingMergeTree](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/aggregatingmergetree) table engine.\n- [finalizeAggregation](https:\/\/clickhouse.com\/docs\/sql-reference\/functions\/other-functions#finalizeAggregation) function.\n- [runningAccumulate](https:\/\/clickhouse.com\/docs\/sql-reference\/functions\/other-functions#runningAccumulate) function.\n- [\\-Merge](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-merge) combinator.\n- [\\-MergeState](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-mergestate) combinator.\n## \\-Merge[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-merge \"Direct link to -Merge\")\nIf you apply this combinator, the aggregate function takes the intermediate aggregation state as an argument, combines the states to finish aggregation, and returns the resulting value.\n\n## \\-MergeState[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-mergestate \"Direct link to -MergeState\")\nMerges the intermediate aggregation states in the same way as the -Merge combinator. However, it does not return the resulting value, but an intermediate aggregation state, similar to the -State combinator.\n\n## \\-ForEach[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-foreach \"Direct link to -ForEach\")\nConverts an aggregate function for tables into an aggregate function for arrays that aggregates the corresponding array items and returns an array of results. For example, `sumForEach` for the arrays `[1, 2]`, `[3, 4, 5]`and`[6, 7]`returns the result `[10, 13, 5]` after adding together the corresponding array items.\n\n## \\-Distinct[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-distinct \"Direct link to -Distinct\")\nEvery unique combination of arguments will be aggregated only once. Repeating values are ignored. Examples: `sum(DISTINCT x)` (or `sumDistinct(x)`), `groupArray(DISTINCT x)` (or `groupArrayDistinct(x)`), `corrStable(DISTINCT x, y)` (or `corrStableDistinct(x, y)`) and so on.\n\n## \\-OrDefault[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-ordefault \"Direct link to -OrDefault\")\nChanges behavior of an aggregate function.\n\nIf an aggregate function does not have input values, with this combinator it returns the default value for its return data type. Applies to the aggregate functions that can take empty input data.\n\n`-OrDefault` can be used with other combinators.\n\n**Syntax**\n```\n<aggFunction>OrDefault(x)\n```\n**Arguments**\n\n- `x` — Aggregate function parameters.\n\n**Returned values**\n\nReturns the default value of an aggregate function's return type if there is nothing to aggregate.\n\nType depends on the aggregate function used.\n\n**Example**\n\nQuery:\n```\nSELECT avg(number), avgOrDefault(number) FROM numbers(0)\n```\nResult:\n```\n┌─avg(number)─┬─avgOrDefault(number)─┐\n│         nan │                    0 │\n└─────────────┴──────────────────────┘\n```\nAlso `-OrDefault` can be used with another combinators. It is useful when the aggregate function does not accept the empty input.\n\nQuery:\n```\nSELECT avgOrDefaultIf(x, x > 10)\nFROM\n(\n    SELECT toDecimal32(1.23, 2) AS x\n)\n```\nResult:\n```\n┌─avgOrDefaultIf(x, greater(x, 10))─┐\n│                              0.00 │\n└───────────────────────────────────┘\n```\n## \\-OrNull[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-ornull \"Direct link to -OrNull\")\nChanges behavior of an aggregate function.\n\nThis combinator converts a result of an aggregate function to the [Nullable](https:\/\/clickhouse.com\/docs\/sql-reference\/data-types\/nullable) data type. If the aggregate function does not have values to calculate it returns [NULL](https:\/\/clickhouse.com\/docs\/operations\/settings\/formats#input_format_null_as_default).\n\n`-OrNull` can be used with other combinators.\n\n**Syntax**\n```\n<aggFunction>OrNull(x)\n```\n**Arguments**\n\n- `x` — Aggregate function parameters.\n\n**Returned values**\n\n- The result of the aggregate function, converted to the `Nullable` data type.\n- `NULL`, if there is nothing to aggregate.\n\nType: `Nullable(aggregate function return type)`.\n\n**Example**\n\nAdd `-orNull` to the end of aggregate function.\n\nQuery:\n```\nSELECT sumOrNull(number), toTypeName(sumOrNull(number)) FROM numbers(10) WHERE number > 10\n```\nResult:\n```\n┌─sumOrNull(number)─┬─toTypeName(sumOrNull(number))─┐\n│              ᴺᵁᴸᴸ │ Nullable(UInt64)              │\n└───────────────────┴───────────────────────────────┘\n```\nAlso `-OrNull` can be used with another combinators. It is useful when the aggregate function does not accept the empty input.\n\nQuery:\n```\nSELECT avgOrNullIf(x, x > 10)\nFROM\n(\n    SELECT toDecimal32(1.23, 2) AS x\n)\n```\nResult:\n```\n┌─avgOrNullIf(x, greater(x, 10))─┐\n│                           ᴺᵁᴸᴸ │\n└────────────────────────────────┘\n```\n## \\-Resample[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-resample \"Direct link to -Resample\")\nLets you divide data into groups, and then separately aggregates the data in those groups. Groups are created by splitting the values from one column into intervals.\n```\n<aggFunction>Resample(start, end, step)(<aggFunction_params>, resampling_key)\n```\n**Arguments**\n\n- `start` — Starting value of the whole required interval for `resampling_key` values.\n- `stop` — Ending value of the whole required interval for `resampling_key` values. The whole interval does not include the `stop` value `[start, stop)`.\n- `step` — Step for separating the whole interval into subintervals. The `aggFunction` is executed over each of those subintervals independently.\n- `resampling_key` — Column whose values are used for separating data into intervals.\n- `aggFunction_params` — `aggFunction` parameters.\n\n**Returned values**\n\n- Array of `aggFunction` results for each subinterval.\n\n**Example**\n\nConsider the `people` table with the following data:\n```\n┌─name───┬─age─┬─wage─┐\n│ John   │  16 │   10 │\n│ Alice  │  30 │   15 │\n│ Mary   │  35 │    8 │\n│ Evelyn │  48 │ 11.5 │\n│ David  │  62 │  9.9 │\n│ Brian  │  60 │   16 │\n└────────┴─────┴──────┘\n```\nLet's get the names of the people whose age lies in the intervals of `[30,60)` and `[60,75)`. Since we use integer representation for age, we get ages in the `[30, 59]` and `[60,74]` intervals.\n\nTo aggregate names in an array, we use the [groupArray](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/reference\/grouparray) aggregate function. It takes one argument. In our case, it's the `name` column. The `groupArrayResample` function should use the `age` column to aggregate names by age. To define the required intervals, we pass the `30, 75, 30` arguments into the `groupArrayResample` function.\n```\nSELECT groupArrayResample(30, 75, 30)(name, age) FROM people\n```\n```\n┌─groupArrayResample(30, 75, 30)(name, age)─────┐\n│ [['Alice','Mary','Evelyn'],['David','Brian']] │\n└───────────────────────────────────────────────┘\n```\nConsider the results.\n\n`John` is out of the sample because he's too young. Other people are distributed according to the specified age intervals.\n\nNow let's count the total number of people and their average wage in the specified age intervals.\n```\nSELECT\n    countResample(30, 75, 30)(name, age) AS amount,\n    avgResample(30, 75, 30)(wage, age) AS avg_wage\nFROM people\n```\n```\n┌─amount─┬─avg_wage──────────────────┐\n│ [3,2]  │ [11.5,12.949999809265137] │\n└────────┴───────────────────────────┘\n```\n## \\-ArgMin[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-argmin \"Direct link to -ArgMin\")\nThe suffix -ArgMin can be appended to the name of any aggregate function. In this case, the aggregate function accepts an additional argument, which should be any comparable expression. The aggregate function processes only the rows that have the minimum value for the specified extra expression.\n\nExamples: `sumArgMin(column, expr)`, `countArgMin(expr)`, `avgArgMin(x, expr)` and so on.\n\n## \\-ArgMax[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#-argmax \"Direct link to -ArgMax\")\nSimilar to suffix -ArgMin but processes only the rows that have the maximum value for the specified extra expression.\n\n## Related Content[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators#related-content \"Direct link to Related Content\")\n- Blog: [Using Aggregate Combinators in ClickHouse](https:\/\/clickhouse.com\/blog\/aggregate-functions-combinators-in-clickhouse-for-arrays-maps-and-states)","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/combinators
Last Crawled	2026-04-04 08:19:26 (2 days ago)
First Indexed	2025-02-21 00:56:27 (1 year ago)
HTTP Status Code	200
Meta Title	Aggregate Function Combinators \| ClickHouse Docs
Meta Description	Documentation for Aggregate Function Combinators
Meta Canonical	null
Boilerpipe Text	The name of an aggregate function can have a suffix appended to it. This changes the way the aggregate function works. -If The suffix -If can be appended to the name of any aggregate function. In this case, the aggregate function accepts an extra argument – a condition (Uint8 type). The aggregate function processes only the rows that trigger the condition. If the condition was not triggered even once, it returns a default value (usually zeros or empty strings). Examples: sumIf(column, cond) , countIf(cond) , avgIf(x, cond) , quantilesTimingIf(level1, level2)(x, cond) , argMinIf(arg, val, cond) and so on. With conditional aggregate functions, you can calculate aggregates for several conditions at once, without using subqueries and JOIN s. For example, conditional aggregate functions can be used to implement the segment comparison functionality. -Array The -Array suffix can be appended to any aggregate function. In this case, the aggregate function takes arguments of the 'Array(T)' type (arrays) instead of 'T' type arguments. If the aggregate function accepts multiple arguments, this must be arrays of equal lengths. When processing arrays, the aggregate function works like the original aggregate function across all array elements. Example 1: sumArray(arr) - Totals all the elements of all 'arr' arrays. In this example, it could have been written more simply: sum(arraySum(arr)) . Example 2: uniqArray(arr) – Counts the number of unique elements in all 'arr' arrays. This could be done an easier way: uniq(arrayJoin(arr)) , but it's not always possible to add 'arrayJoin' to a query. -If and -Array can be combined. However, 'Array' must come first, then 'If'. Examples: uniqArrayIf(arr, cond) , quantilesTimingArrayIf(level1, level2)(arr, cond) . Due to this order, the 'cond' argument won't be an array. -Map The -Map suffix can be appended to any aggregate function. This will create an aggregate function which gets Map type as an argument, and aggregates values of each key of the map separately using the specified aggregate function. The result is also of a Map type. Example CREATE TABLE map_map ( date Date , timeslot DateTime , status Map ( String , UInt64 ) ) ENGINE = MergeTree ORDER BY ( ) ; INSERT INTO map_map VALUES ( '2000-01-01' , '2000-01-01 00:00:00' , ( [ 'a' , 'b' , 'c' ] , [ 10 , 10 , 10 ] ) ) , ( '2000-01-01' , '2000-01-01 00:00:00' , ( [ 'c' , 'd' , 'e' ] , [ 10 , 10 , 10 ] ) ) , ( '2000-01-01' , '2000-01-01 00:01:00' , ( [ 'd' , 'e' , 'f' ] , [ 10 , 10 , 10 ] ) ) , ( '2000-01-01' , '2000-01-01 00:01:00' , ( [ 'f' , 'g' , 'g' ] , [ 10 , 10 , 10 ] ) ) ; SELECT timeslot , sumMap ( status ) , avgMap ( status ) , minMap ( status ) FROM map_map GROUP BY timeslot ; ┌────────────timeslot─┬─sumMap ( status ) ───────────────────────┬─avgMap ( status ) ───────────────────────┬─minMap ( status ) ───────────────────────┐ │ 2000 - 01 - 01 00 : 00 : 00 │ { 'a' : 10 , 'b' : 10 , 'c' : 20 , 'd' : 10 , 'e' : 10 } │ { 'a' : 10 , 'b' : 10 , 'c' : 10 , 'd' : 10 , 'e' : 10 } │ { 'a' : 10 , 'b' : 10 , 'c' : 10 , 'd' : 10 , 'e' : 10 } │ │ 2000 - 01 - 01 00 : 01 : 00 │ { 'd' : 10 , 'e' : 10 , 'f' : 20 , 'g' : 20 } │ { 'd' : 10 , 'e' : 10 , 'f' : 10 , 'g' : 10 } │ { 'd' : 10 , 'e' : 10 , 'f' : 10 , 'g' : 10 } │ └─────────────────────┴──────────────────────────────────────┴──────────────────────────────────────┴──────────────────────────────────────┘ -SimpleState If you apply this combinator, the aggregate function returns the same value but with a different type. This is a SimpleAggregateFunction(...) that can be stored in a table to work with AggregatingMergeTree tables. Syntax < aggFunction > SimpleState ( x ) Arguments x — Aggregate function parameters. Returned values The value of an aggregate function with the SimpleAggregateFunction(...) type. Example Query: WITH anySimpleState ( number ) AS c SELECT toTypeName ( c ) , c FROM numbers ( 1 ) ; Result: ┌─toTypeName(c)────────────────────────┬─c─┐ │ SimpleAggregateFunction(any, UInt64) │ 0 │ └──────────────────────────────────────┴───┘ -State If you apply this combinator, the aggregate function does not return the resulting value (such as the number of unique values for the uniq function), but an intermediate state of the aggregation (for uniq , this is the hash table for calculating the number of unique values). This is an AggregateFunction(...) that can be used for further processing or stored in a table to finish aggregating later. Note Please notice, that -MapState is not an invariant for the same data due to the fact that order of data in intermediate state can change, though it doesn't impact ingestion of this data. To work with these states, use: AggregatingMergeTree table engine. finalizeAggregation function. runningAccumulate function. -Merge combinator. -MergeState combinator. -Merge If you apply this combinator, the aggregate function takes the intermediate aggregation state as an argument, combines the states to finish aggregation, and returns the resulting value. -MergeState Merges the intermediate aggregation states in the same way as the -Merge combinator. However, it does not return the resulting value, but an intermediate aggregation state, similar to the -State combinator. -ForEach Converts an aggregate function for tables into an aggregate function for arrays that aggregates the corresponding array items and returns an array of results. For example, sumForEach for the arrays [1, 2] , [3, 4, 5] and [6, 7] returns the result [10, 13, 5] after adding together the corresponding array items. -Distinct Every unique combination of arguments will be aggregated only once. Repeating values are ignored. Examples: sum(DISTINCT x) (or sumDistinct(x) ), groupArray(DISTINCT x) (or groupArrayDistinct(x) ), corrStable(DISTINCT x, y) (or corrStableDistinct(x, y) ) and so on. -OrDefault Changes behavior of an aggregate function. If an aggregate function does not have input values, with this combinator it returns the default value for its return data type. Applies to the aggregate functions that can take empty input data. -OrDefault can be used with other combinators. Syntax < aggFunction > OrDefault ( x ) Arguments x — Aggregate function parameters. Returned values Returns the default value of an aggregate function's return type if there is nothing to aggregate. Type depends on the aggregate function used. Example Query: SELECT avg ( number ) , avgOrDefault ( number ) FROM numbers ( 0 ) Result: ┌─avg(number)─┬─avgOrDefault(number)─┐ │ nan │ 0 │ └─────────────┴──────────────────────┘ Also -OrDefault can be used with another combinators. It is useful when the aggregate function does not accept the empty input. Query: SELECT avgOrDefaultIf ( x , x > 10 ) FROM ( SELECT toDecimal32 ( 1.23 , 2 ) AS x ) Result: ┌─avgOrDefaultIf(x, greater(x, 10))─┐ │ 0.00 │ └───────────────────────────────────┘ -OrNull Changes behavior of an aggregate function. This combinator converts a result of an aggregate function to the Nullable data type. If the aggregate function does not have values to calculate it returns NULL . -OrNull can be used with other combinators. Syntax < aggFunction > OrNull ( x ) Arguments x — Aggregate function parameters. Returned values The result of the aggregate function, converted to the Nullable data type. NULL , if there is nothing to aggregate. Type: Nullable(aggregate function return type) . Example Add -orNull to the end of aggregate function. Query: SELECT sumOrNull ( number ) , toTypeName ( sumOrNull ( number ) ) FROM numbers ( 10 ) WHERE number > 10 Result: ┌─sumOrNull(number)─┬─toTypeName(sumOrNull(number))─┐ │ ᴺᵁᴸᴸ │ Nullable(UInt64) │ └───────────────────┴───────────────────────────────┘ Also -OrNull can be used with another combinators. It is useful when the aggregate function does not accept the empty input. Query: SELECT avgOrNullIf ( x , x > 10 ) FROM ( SELECT toDecimal32 ( 1.23 , 2 ) AS x ) Result: ┌─avgOrNullIf(x, greater(x, 10))─┐ │ ᴺᵁᴸᴸ │ └────────────────────────────────┘ -Resample Lets you divide data into groups, and then separately aggregates the data in those groups. Groups are created by splitting the values from one column into intervals. < aggFunction > Resample ( start , end , step ) ( < aggFunction_params > , resampling_key ) Arguments start — Starting value of the whole required interval for resampling_key values. stop — Ending value of the whole required interval for resampling_key values. The whole interval does not include the stop value [start, stop) . step — Step for separating the whole interval into subintervals. The aggFunction is executed over each of those subintervals independently. resampling_key — Column whose values are used for separating data into intervals. aggFunction_params — aggFunction parameters. Returned values Array of aggFunction results for each subinterval. Example Consider the people table with the following data: ┌─name───┬─age─┬─wage─┐ │ John │ 16 │ 10 │ │ Alice │ 30 │ 15 │ │ Mary │ 35 │ 8 │ │ Evelyn │ 48 │ 11.5 │ │ David │ 62 │ 9.9 │ │ Brian │ 60 │ 16 │ └────────┴─────┴──────┘ Let's get the names of the people whose age lies in the intervals of [30,60) and [60,75) . Since we use integer representation for age, we get ages in the [30, 59] and [60,74] intervals. To aggregate names in an array, we use the groupArray aggregate function. It takes one argument. In our case, it's the name column. The groupArrayResample function should use the age column to aggregate names by age. To define the required intervals, we pass the 30, 75, 30 arguments into the groupArrayResample function. SELECT groupArrayResample ( 30 , 75 , 30 ) ( name , age ) FROM people ┌─groupArrayResample(30, 75, 30)(name, age)─────┐ │ [['Alice','Mary','Evelyn'],['David','Brian']] │ └───────────────────────────────────────────────┘ Consider the results. John is out of the sample because he's too young. Other people are distributed according to the specified age intervals. Now let's count the total number of people and their average wage in the specified age intervals. SELECT countResample ( 30 , 75 , 30 ) ( name , age ) AS amount , avgResample ( 30 , 75 , 30 ) ( wage , age ) AS avg_wage FROM people ┌─amount─┬─avg_wage──────────────────┐ │ [3,2] │ [11.5,12.949999809265137] │ └────────┴───────────────────────────┘ -ArgMin The suffix -ArgMin can be appended to the name of any aggregate function. In this case, the aggregate function accepts an additional argument, which should be any comparable expression. The aggregate function processes only the rows that have the minimum value for the specified extra expression. Examples: sumArgMin(column, expr) , countArgMin(expr) , avgArgMin(x, expr) and so on. -ArgMax Similar to suffix -ArgMin but processes only the rows that have the maximum value for the specified extra expression. Related Content Blog: Using Aggregate Combinators in ClickHouse
Markdown	[Skip to main content](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#__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/combinators) - [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/combinators) - [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/combinators) - [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/combinators) - [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\.7k](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=43f221c6-40d4-4576-931b-74d90e353b1e&pagePath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fcombinators&origPath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fcombinators&utm_ga=GA1.1.657831716.1775290767) [Get started](https://console.clickhouse.cloud/signUp?loc=docs-nav-signUp-cta&glxid=43f221c6-40d4-4576-931b-74d90e353b1e&pagePath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fcombinators&origPath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fcombinators&utm_ga=GA1.1.657831716.1775290767) [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/combinators) - [English](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators) - [日本語](https://clickhouse.com/docs/jp/sql-reference/aggregate-functions/combinators) - [中文](https://clickhouse.com/docs/zh/sql-reference/aggregate-functions/combinators) - [Русский](https://clickhouse.com/docs/ru/sql-reference/aggregate-functions/combinators) - [한국어](https://clickhouse.com/docs/ko/sql-reference/aggregate-functions/combinators) [Skip to main content](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#__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/combinators) - [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/combinators) - [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/combinators) - [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/combinators) - [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\.7k](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=43f221c6-40d4-4576-931b-74d90e353b1e&pagePath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fcombinators&origPath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fcombinators&utm_ga=GA1.1.657831716.1775290767) [Get started](https://console.clickhouse.cloud/signUp?loc=docs-nav-signUp-cta&glxid=43f221c6-40d4-4576-931b-74d90e353b1e&pagePath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fcombinators&origPath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fcombinators&utm_ga=GA1.1.657831716.1775290767) [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/combinators) - [English](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators) - [日本語](https://clickhouse.com/docs/jp/sql-reference/aggregate-functions/combinators) - [中文](https://clickhouse.com/docs/zh/sql-reference/aggregate-functions/combinators) - [Русский](https://clickhouse.com/docs/ru/sql-reference/aggregate-functions/combinators) - [한국어](https://clickhouse.com/docs/ko/sql-reference/aggregate-functions/combinators) [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/combinators) - [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) - Combinators [Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/aggregate-functions/combinators.md) # Aggregate function combinators The name of an aggregate function can have a suffix appended to it. This changes the way the aggregate function works. ## \-If[](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-if "Direct link to -If") The suffix -If can be appended to the name of any aggregate function. In this case, the aggregate function accepts an extra argument – a condition (Uint8 type). The aggregate function processes only the rows that trigger the condition. If the condition was not triggered even once, it returns a default value (usually zeros or empty strings). Examples: `sumIf(column, cond)`, `countIf(cond)`, `avgIf(x, cond)`, `quantilesTimingIf(level1, level2)(x, cond)`, `argMinIf(arg, val, cond)` and so on. With conditional aggregate functions, you can calculate aggregates for several conditions at once, without using subqueries and `JOIN`s. For example, conditional aggregate functions can be used to implement the segment comparison functionality. ## \-Array[](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-array "Direct link to -Array") The -Array suffix can be appended to any aggregate function. In this case, the aggregate function takes arguments of the 'Array(T)' type (arrays) instead of 'T' type arguments. If the aggregate function accepts multiple arguments, this must be arrays of equal lengths. When processing arrays, the aggregate function works like the original aggregate function across all array elements. Example 1: `sumArray(arr)` - Totals all the elements of all 'arr' arrays. In this example, it could have been written more simply: `sum(arraySum(arr))`. Example 2: `uniqArray(arr)` – Counts the number of unique elements in all 'arr' arrays. This could be done an easier way: `uniq(arrayJoin(arr))`, but it's not always possible to add 'arrayJoin' to a query. \-If and -Array can be combined. However, 'Array' must come first, then 'If'. Examples: `uniqArrayIf(arr, cond)`, `quantilesTimingArrayIf(level1, level2)(arr, cond)`. Due to this order, the 'cond' argument won't be an array. ## \-Map[](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-map "Direct link to -Map") The -Map suffix can be appended to any aggregate function. This will create an aggregate function which gets Map type as an argument, and aggregates values of each key of the map separately using the specified aggregate function. The result is also of a Map type. Example ``` CREATE TABLE map_map( date Date, timeslot DateTime, status Map(String, UInt64) ) ENGINE = MergeTree ORDER BY (); INSERT INTO map_map VALUES ('2000-01-01', '2000-01-01 00:00:00', (['a', 'b', 'c'], [10, 10, 10])), ('2000-01-01', '2000-01-01 00:00:00', (['c', 'd', 'e'], [10, 10, 10])), ('2000-01-01', '2000-01-01 00:01:00', (['d', 'e', 'f'], [10, 10, 10])), ('2000-01-01', '2000-01-01 00:01:00', (['f', 'g', 'g'], [10, 10, 10])); SELECT timeslot, sumMap(status), avgMap(status), minMap(status) FROM map_map GROUP BY timeslot; ┌────────────timeslot─┬─sumMap(status)───────────────────────┬─avgMap(status)───────────────────────┬─minMap(status)───────────────────────┐ │ 2000-01-01 00:00:00 │ {'a':10,'b':10,'c':20,'d':10,'e':10} │ {'a':10,'b':10,'c':10,'d':10,'e':10} │ {'a':10,'b':10,'c':10,'d':10,'e':10} │ │ 2000-01-01 00:01:00 │ {'d':10,'e':10,'f':20,'g':20} │ {'d':10,'e':10,'f':10,'g':10} │ {'d':10,'e':10,'f':10,'g':10} │ └─────────────────────┴──────────────────────────────────────┴──────────────────────────────────────┴──────────────────────────────────────┘ ``` ## \-SimpleState[](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-simplestate "Direct link to -SimpleState") If you apply this combinator, the aggregate function returns the same value but with a different type. This is a [SimpleAggregateFunction(...)](https://clickhouse.com/docs/sql-reference/data-types/simpleaggregatefunction) that can be stored in a table to work with [AggregatingMergeTree](https://clickhouse.com/docs/engines/table-engines/mergetree-family/aggregatingmergetree) tables. Syntax ``` <aggFunction>SimpleState(x) ``` Arguments - `x` — Aggregate function parameters. Returned values The value of an aggregate function with the `SimpleAggregateFunction(...)` type. Example Query: ``` WITH anySimpleState(number) AS c SELECT toTypeName(c), c FROM numbers(1); ``` Result: ``` ┌─toTypeName(c)────────────────────────┬─c─┐ │ SimpleAggregateFunction(any, UInt64) │ 0 │ └──────────────────────────────────────┴───┘ ``` ## \-State[](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-state "Direct link to -State") If you apply this combinator, the aggregate function does not return the resulting value (such as the number of unique values for the [uniq](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/uniq) function), but an intermediate state of the aggregation (for `uniq`, this is the hash table for calculating the number of unique values). This is an `AggregateFunction(...)` that can be used for further processing or stored in a table to finish aggregating later. Note Please notice, that -MapState is not an invariant for the same data due to the fact that order of data in intermediate state can change, though it doesn't impact ingestion of this data. To work with these states, use: - [AggregatingMergeTree](https://clickhouse.com/docs/engines/table-engines/mergetree-family/aggregatingmergetree) table engine. - [finalizeAggregation](https://clickhouse.com/docs/sql-reference/functions/other-functions#finalizeAggregation) function. - [runningAccumulate](https://clickhouse.com/docs/sql-reference/functions/other-functions#runningAccumulate) function. - [\-Merge](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-merge) combinator. - [\-MergeState](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-mergestate) combinator. ## \-Merge[](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-merge "Direct link to -Merge") If you apply this combinator, the aggregate function takes the intermediate aggregation state as an argument, combines the states to finish aggregation, and returns the resulting value. ## \-MergeState[](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-mergestate "Direct link to -MergeState") Merges the intermediate aggregation states in the same way as the -Merge combinator. However, it does not return the resulting value, but an intermediate aggregation state, similar to the -State combinator. ## \-ForEach[](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-foreach "Direct link to -ForEach") Converts an aggregate function for tables into an aggregate function for arrays that aggregates the corresponding array items and returns an array of results. For example, `sumForEach` for the arrays `[1, 2]`, `[3, 4, 5]`and`[6, 7]`returns the result `[10, 13, 5]` after adding together the corresponding array items. ## \-Distinct[](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-distinct "Direct link to -Distinct") Every unique combination of arguments will be aggregated only once. Repeating values are ignored. Examples: `sum(DISTINCT x)` (or `sumDistinct(x)`), `groupArray(DISTINCT x)` (or `groupArrayDistinct(x)`), `corrStable(DISTINCT x, y)` (or `corrStableDistinct(x, y)`) and so on. ## \-OrDefault[](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-ordefault "Direct link to -OrDefault") Changes behavior of an aggregate function. If an aggregate function does not have input values, with this combinator it returns the default value for its return data type. Applies to the aggregate functions that can take empty input data. `-OrDefault` can be used with other combinators. Syntax ``` <aggFunction>OrDefault(x) ``` Arguments - `x` — Aggregate function parameters. Returned values Returns the default value of an aggregate function's return type if there is nothing to aggregate. Type depends on the aggregate function used. Example Query: ``` SELECT avg(number), avgOrDefault(number) FROM numbers(0) ``` Result: ``` ┌─avg(number)─┬─avgOrDefault(number)─┐ │ nan │ 0 │ └─────────────┴──────────────────────┘ ``` Also `-OrDefault` can be used with another combinators. It is useful when the aggregate function does not accept the empty input. Query: ``` SELECT avgOrDefaultIf(x, x > 10) FROM ( SELECT toDecimal32(1.23, 2) AS x ) ``` Result: ``` ┌─avgOrDefaultIf(x, greater(x, 10))─┐ │ 0.00 │ └───────────────────────────────────┘ ``` ## \-OrNull[](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-ornull "Direct link to -OrNull") Changes behavior of an aggregate function. This combinator converts a result of an aggregate function to the [Nullable](https://clickhouse.com/docs/sql-reference/data-types/nullable) data type. If the aggregate function does not have values to calculate it returns [NULL](https://clickhouse.com/docs/operations/settings/formats#input_format_null_as_default). `-OrNull` can be used with other combinators. Syntax ``` <aggFunction>OrNull(x) ``` Arguments - `x` — Aggregate function parameters. Returned values - The result of the aggregate function, converted to the `Nullable` data type. - `NULL`, if there is nothing to aggregate. Type: `Nullable(aggregate function return type)`. Example Add `-orNull` to the end of aggregate function. Query: ``` SELECT sumOrNull(number), toTypeName(sumOrNull(number)) FROM numbers(10) WHERE number > 10 ``` Result: ``` ┌─sumOrNull(number)─┬─toTypeName(sumOrNull(number))─┐ │ ᴺᵁᴸᴸ │ Nullable(UInt64) │ └───────────────────┴───────────────────────────────┘ ``` Also `-OrNull` can be used with another combinators. It is useful when the aggregate function does not accept the empty input. Query: ``` SELECT avgOrNullIf(x, x > 10) FROM ( SELECT toDecimal32(1.23, 2) AS x ) ``` Result: ``` ┌─avgOrNullIf(x, greater(x, 10))─┐ │ ᴺᵁᴸᴸ │ └────────────────────────────────┘ ``` ## \-Resample[](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-resample "Direct link to -Resample") Lets you divide data into groups, and then separately aggregates the data in those groups. Groups are created by splitting the values from one column into intervals. ``` <aggFunction>Resample(start, end, step)(<aggFunction_params>, resampling_key) ``` Arguments - `start` — Starting value of the whole required interval for `resampling_key` values. - `stop` — Ending value of the whole required interval for `resampling_key` values. The whole interval does not include the `stop` value `[start, stop)`. - `step` — Step for separating the whole interval into subintervals. The `aggFunction` is executed over each of those subintervals independently. - `resampling_key` — Column whose values are used for separating data into intervals. - `aggFunction_params` — `aggFunction` parameters. Returned values - Array of `aggFunction` results for each subinterval. Example Consider the `people` table with the following data: ``` ┌─name───┬─age─┬─wage─┐ │ John │ 16 │ 10 │ │ Alice │ 30 │ 15 │ │ Mary │ 35 │ 8 │ │ Evelyn │ 48 │ 11.5 │ │ David │ 62 │ 9.9 │ │ Brian │ 60 │ 16 │ └────────┴─────┴──────┘ ``` Let's get the names of the people whose age lies in the intervals of `[30,60)` and `[60,75)`. Since we use integer representation for age, we get ages in the `[30, 59]` and `[60,74]` intervals. To aggregate names in an array, we use the [groupArray](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/grouparray) aggregate function. It takes one argument. In our case, it's the `name` column. The `groupArrayResample` function should use the `age` column to aggregate names by age. To define the required intervals, we pass the `30, 75, 30` arguments into the `groupArrayResample` function. ``` SELECT groupArrayResample(30, 75, 30)(name, age) FROM people ``` ``` ┌─groupArrayResample(30, 75, 30)(name, age)─────┐ │ [['Alice','Mary','Evelyn'],['David','Brian']] │ └───────────────────────────────────────────────┘ ``` Consider the results. `John` is out of the sample because he's too young. Other people are distributed according to the specified age intervals. Now let's count the total number of people and their average wage in the specified age intervals. ``` SELECT countResample(30, 75, 30)(name, age) AS amount, avgResample(30, 75, 30)(wage, age) AS avg_wage FROM people ``` ``` ┌─amount─┬─avg_wage──────────────────┐ │ [3,2] │ [11.5,12.949999809265137] │ └────────┴───────────────────────────┘ ``` ## \-ArgMin[](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-argmin "Direct link to -ArgMin") The suffix -ArgMin can be appended to the name of any aggregate function. In this case, the aggregate function accepts an additional argument, which should be any comparable expression. The aggregate function processes only the rows that have the minimum value for the specified extra expression. Examples: `sumArgMin(column, expr)`, `countArgMin(expr)`, `avgArgMin(x, expr)` and so on. ## \-ArgMax[](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-argmax "Direct link to -ArgMax") Similar to suffix -ArgMin but processes only the rows that have the maximum value for the specified extra expression. ## Related Content[](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#related-content "Direct link to Related Content") - Blog: [Using Aggregate Combinators in ClickHouse](https://clickhouse.com/blog/aggregate-functions-combinators-in-clickhouse-for-arrays-maps-and-states) [Previous welchTTest](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/welchttest) [Next Parametric](https://clickhouse.com/docs/sql-reference/aggregate-functions/parametric-functions) - [\-If](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-if) - [\-Array](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-array) - [\-Map](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-map) - [\-SimpleState](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-simplestate) - [\-State](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-state) - [\-Merge](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-merge) - [\-MergeState](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-mergestate) - [\-ForEach](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-foreach) - [\-Distinct](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-distinct) - [\-OrDefault](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-ordefault) - [\-OrNull](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-ornull) - [\-Resample](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-resample) - [\-ArgMin](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-argmin) - [\-ArgMax](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-argmax) - [Related Content](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#related-content) 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=43f221c6-40d4-4576-931b-74d90e353b1e&pagePath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fcombinators&origPath=%2Fdocs%2Fsql-reference%2Faggregate-functions%2Fcombinators&utm_ga=GA1.1.657831716.1775290767) © 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	The name of an aggregate function can have a suffix appended to it. This changes the way the aggregate function works. ## \-If[](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-if "Direct link to -If") The suffix -If can be appended to the name of any aggregate function. In this case, the aggregate function accepts an extra argument – a condition (Uint8 type). The aggregate function processes only the rows that trigger the condition. If the condition was not triggered even once, it returns a default value (usually zeros or empty strings). Examples: `sumIf(column, cond)`, `countIf(cond)`, `avgIf(x, cond)`, `quantilesTimingIf(level1, level2)(x, cond)`, `argMinIf(arg, val, cond)` and so on. With conditional aggregate functions, you can calculate aggregates for several conditions at once, without using subqueries and `JOIN`s. For example, conditional aggregate functions can be used to implement the segment comparison functionality. ## \-Array[](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-array "Direct link to -Array") The -Array suffix can be appended to any aggregate function. In this case, the aggregate function takes arguments of the 'Array(T)' type (arrays) instead of 'T' type arguments. If the aggregate function accepts multiple arguments, this must be arrays of equal lengths. When processing arrays, the aggregate function works like the original aggregate function across all array elements. Example 1: `sumArray(arr)` - Totals all the elements of all 'arr' arrays. In this example, it could have been written more simply: `sum(arraySum(arr))`. Example 2: `uniqArray(arr)` – Counts the number of unique elements in all 'arr' arrays. This could be done an easier way: `uniq(arrayJoin(arr))`, but it's not always possible to add 'arrayJoin' to a query. \-If and -Array can be combined. However, 'Array' must come first, then 'If'. Examples: `uniqArrayIf(arr, cond)`, `quantilesTimingArrayIf(level1, level2)(arr, cond)`. Due to this order, the 'cond' argument won't be an array. ## \-Map[](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-map "Direct link to -Map") The -Map suffix can be appended to any aggregate function. This will create an aggregate function which gets Map type as an argument, and aggregates values of each key of the map separately using the specified aggregate function. The result is also of a Map type. Example ``` CREATE TABLE map_map( date Date, timeslot DateTime, status Map(String, UInt64) ) ENGINE = MergeTree ORDER BY (); INSERT INTO map_map VALUES ('2000-01-01', '2000-01-01 00:00:00', (['a', 'b', 'c'], [10, 10, 10])), ('2000-01-01', '2000-01-01 00:00:00', (['c', 'd', 'e'], [10, 10, 10])), ('2000-01-01', '2000-01-01 00:01:00', (['d', 'e', 'f'], [10, 10, 10])), ('2000-01-01', '2000-01-01 00:01:00', (['f', 'g', 'g'], [10, 10, 10])); SELECT timeslot, sumMap(status), avgMap(status), minMap(status) FROM map_map GROUP BY timeslot; ┌────────────timeslot─┬─sumMap(status)───────────────────────┬─avgMap(status)───────────────────────┬─minMap(status)───────────────────────┐ │ 2000-01-01 00:00:00 │ {'a':10,'b':10,'c':20,'d':10,'e':10} │ {'a':10,'b':10,'c':10,'d':10,'e':10} │ {'a':10,'b':10,'c':10,'d':10,'e':10} │ │ 2000-01-01 00:01:00 │ {'d':10,'e':10,'f':20,'g':20} │ {'d':10,'e':10,'f':10,'g':10} │ {'d':10,'e':10,'f':10,'g':10} │ └─────────────────────┴──────────────────────────────────────┴──────────────────────────────────────┴──────────────────────────────────────┘ ``` ## \-SimpleState[](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-simplestate "Direct link to -SimpleState") If you apply this combinator, the aggregate function returns the same value but with a different type. This is a [SimpleAggregateFunction(...)](https://clickhouse.com/docs/sql-reference/data-types/simpleaggregatefunction) that can be stored in a table to work with [AggregatingMergeTree](https://clickhouse.com/docs/engines/table-engines/mergetree-family/aggregatingmergetree) tables. Syntax ``` <aggFunction>SimpleState(x) ``` Arguments - `x` — Aggregate function parameters. Returned values The value of an aggregate function with the `SimpleAggregateFunction(...)` type. Example Query: ``` WITH anySimpleState(number) AS c SELECT toTypeName(c), c FROM numbers(1); ``` Result: ``` ┌─toTypeName(c)────────────────────────┬─c─┐ │ SimpleAggregateFunction(any, UInt64) │ 0 │ └──────────────────────────────────────┴───┘ ``` ## \-State[](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-state "Direct link to -State") If you apply this combinator, the aggregate function does not return the resulting value (such as the number of unique values for the [uniq](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/uniq) function), but an intermediate state of the aggregation (for `uniq`, this is the hash table for calculating the number of unique values). This is an `AggregateFunction(...)` that can be used for further processing or stored in a table to finish aggregating later. Note Please notice, that -MapState is not an invariant for the same data due to the fact that order of data in intermediate state can change, though it doesn't impact ingestion of this data. To work with these states, use: - [AggregatingMergeTree](https://clickhouse.com/docs/engines/table-engines/mergetree-family/aggregatingmergetree) table engine. - [finalizeAggregation](https://clickhouse.com/docs/sql-reference/functions/other-functions#finalizeAggregation) function. - [runningAccumulate](https://clickhouse.com/docs/sql-reference/functions/other-functions#runningAccumulate) function. - [\-Merge](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-merge) combinator. - [\-MergeState](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-mergestate) combinator. ## \-Merge[](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-merge "Direct link to -Merge") If you apply this combinator, the aggregate function takes the intermediate aggregation state as an argument, combines the states to finish aggregation, and returns the resulting value. ## \-MergeState[](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-mergestate "Direct link to -MergeState") Merges the intermediate aggregation states in the same way as the -Merge combinator. However, it does not return the resulting value, but an intermediate aggregation state, similar to the -State combinator. ## \-ForEach[](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-foreach "Direct link to -ForEach") Converts an aggregate function for tables into an aggregate function for arrays that aggregates the corresponding array items and returns an array of results. For example, `sumForEach` for the arrays `[1, 2]`, `[3, 4, 5]`and`[6, 7]`returns the result `[10, 13, 5]` after adding together the corresponding array items. ## \-Distinct[](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-distinct "Direct link to -Distinct") Every unique combination of arguments will be aggregated only once. Repeating values are ignored. Examples: `sum(DISTINCT x)` (or `sumDistinct(x)`), `groupArray(DISTINCT x)` (or `groupArrayDistinct(x)`), `corrStable(DISTINCT x, y)` (or `corrStableDistinct(x, y)`) and so on. ## \-OrDefault[](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-ordefault "Direct link to -OrDefault") Changes behavior of an aggregate function. If an aggregate function does not have input values, with this combinator it returns the default value for its return data type. Applies to the aggregate functions that can take empty input data. `-OrDefault` can be used with other combinators. Syntax ``` <aggFunction>OrDefault(x) ``` Arguments - `x` — Aggregate function parameters. Returned values Returns the default value of an aggregate function's return type if there is nothing to aggregate. Type depends on the aggregate function used. Example Query: ``` SELECT avg(number), avgOrDefault(number) FROM numbers(0) ``` Result: ``` ┌─avg(number)─┬─avgOrDefault(number)─┐ │ nan │ 0 │ └─────────────┴──────────────────────┘ ``` Also `-OrDefault` can be used with another combinators. It is useful when the aggregate function does not accept the empty input. Query: ``` SELECT avgOrDefaultIf(x, x > 10) FROM ( SELECT toDecimal32(1.23, 2) AS x ) ``` Result: ``` ┌─avgOrDefaultIf(x, greater(x, 10))─┐ │ 0.00 │ └───────────────────────────────────┘ ``` ## \-OrNull[](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-ornull "Direct link to -OrNull") Changes behavior of an aggregate function. This combinator converts a result of an aggregate function to the [Nullable](https://clickhouse.com/docs/sql-reference/data-types/nullable) data type. If the aggregate function does not have values to calculate it returns [NULL](https://clickhouse.com/docs/operations/settings/formats#input_format_null_as_default). `-OrNull` can be used with other combinators. Syntax ``` <aggFunction>OrNull(x) ``` Arguments - `x` — Aggregate function parameters. Returned values - The result of the aggregate function, converted to the `Nullable` data type. - `NULL`, if there is nothing to aggregate. Type: `Nullable(aggregate function return type)`. Example Add `-orNull` to the end of aggregate function. Query: ``` SELECT sumOrNull(number), toTypeName(sumOrNull(number)) FROM numbers(10) WHERE number > 10 ``` Result: ``` ┌─sumOrNull(number)─┬─toTypeName(sumOrNull(number))─┐ │ ᴺᵁᴸᴸ │ Nullable(UInt64) │ └───────────────────┴───────────────────────────────┘ ``` Also `-OrNull` can be used with another combinators. It is useful when the aggregate function does not accept the empty input. Query: ``` SELECT avgOrNullIf(x, x > 10) FROM ( SELECT toDecimal32(1.23, 2) AS x ) ``` Result: ``` ┌─avgOrNullIf(x, greater(x, 10))─┐ │ ᴺᵁᴸᴸ │ └────────────────────────────────┘ ``` ## \-Resample[](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-resample "Direct link to -Resample") Lets you divide data into groups, and then separately aggregates the data in those groups. Groups are created by splitting the values from one column into intervals. ``` <aggFunction>Resample(start, end, step)(<aggFunction_params>, resampling_key) ``` Arguments - `start` — Starting value of the whole required interval for `resampling_key` values. - `stop` — Ending value of the whole required interval for `resampling_key` values. The whole interval does not include the `stop` value `[start, stop)`. - `step` — Step for separating the whole interval into subintervals. The `aggFunction` is executed over each of those subintervals independently. - `resampling_key` — Column whose values are used for separating data into intervals. - `aggFunction_params` — `aggFunction` parameters. Returned values - Array of `aggFunction` results for each subinterval. Example Consider the `people` table with the following data: ``` ┌─name───┬─age─┬─wage─┐ │ John │ 16 │ 10 │ │ Alice │ 30 │ 15 │ │ Mary │ 35 │ 8 │ │ Evelyn │ 48 │ 11.5 │ │ David │ 62 │ 9.9 │ │ Brian │ 60 │ 16 │ └────────┴─────┴──────┘ ``` Let's get the names of the people whose age lies in the intervals of `[30,60)` and `[60,75)`. Since we use integer representation for age, we get ages in the `[30, 59]` and `[60,74]` intervals. To aggregate names in an array, we use the [groupArray](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/grouparray) aggregate function. It takes one argument. In our case, it's the `name` column. The `groupArrayResample` function should use the `age` column to aggregate names by age. To define the required intervals, we pass the `30, 75, 30` arguments into the `groupArrayResample` function. ``` SELECT groupArrayResample(30, 75, 30)(name, age) FROM people ``` ``` ┌─groupArrayResample(30, 75, 30)(name, age)─────┐ │ [['Alice','Mary','Evelyn'],['David','Brian']] │ └───────────────────────────────────────────────┘ ``` Consider the results. `John` is out of the sample because he's too young. Other people are distributed according to the specified age intervals. Now let's count the total number of people and their average wage in the specified age intervals. ``` SELECT countResample(30, 75, 30)(name, age) AS amount, avgResample(30, 75, 30)(wage, age) AS avg_wage FROM people ``` ``` ┌─amount─┬─avg_wage──────────────────┐ │ [3,2] │ [11.5,12.949999809265137] │ └────────┴───────────────────────────┘ ``` ## \-ArgMin[](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-argmin "Direct link to -ArgMin") The suffix -ArgMin can be appended to the name of any aggregate function. In this case, the aggregate function accepts an additional argument, which should be any comparable expression. The aggregate function processes only the rows that have the minimum value for the specified extra expression. Examples: `sumArgMin(column, expr)`, `countArgMin(expr)`, `avgArgMin(x, expr)` and so on. ## \-ArgMax[](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-argmax "Direct link to -ArgMax") Similar to suffix -ArgMin but processes only the rows that have the maximum value for the specified extra expression. ## Related Content[](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#related-content "Direct link to Related Content") - Blog: [Using Aggregate Combinators in ClickHouse](https://clickhouse.com/blog/aggregate-functions-combinators-in-clickhouse-for-arrays-maps-and-states)
Shard	89 (laksa)
Root Hash	12633450985039531489
Unparsed URL	com,clickhouse!/docs/sql-reference/aggregate-functions/combinators s443