🕷️ Crawler Inspector

URL Lookup

Direct Parameter Lookup

Raw Queries and Responses

1. Shard Calculation

Query:

Response:

Calculated Shard: 89 (from laksa146)

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

Response:

{"found_url":"https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions","crawl_time":1775876980,"first_indexed_time":1740098001,"http_code":200,"src_unparsed":"com,clickhouse!\/docs\/sql-reference\/aggregate-functions s443","src_root_hash":"12633450985039531489","history_drop_reason":null,"meta_title":"Aggregate Functions | ClickHouse Docs","meta_descriptions":["Documentation for Aggregate Functions"],"attrs_boilerpipe_text":"Aggregate functions work in the\nnormal\nway as expected by database experts.\nClickHouse also supports:\nParametric aggregate functions\n, which accept other parameters in addition to columns.\nCombinators\n, which change the behavior of aggregate functions.\nNULL processing\n\nDuring aggregation, all\nNULL\narguments are skipped. If the aggregation has several arguments it will ignore any row in which one or more of them are NULL.\nThere is an exception to this rule, which are the functions\nfirst_value\n,\nlast_value\nand their aliases (\nany\nand\nanyLast\nrespectively) when followed by the modifier\nRESPECT NULLS\n. For example,\nFIRST_VALUE(b) RESPECT NULLS\n.\nExamples:\nConsider this table:\n┌─x─┬────y─┐\n│ 1 │    2 │\n│ 2 │ ᴺᵁᴸᴸ │\n│ 3 │    2 │\n│ 3 │    3 │\n│ 3 │ ᴺᵁᴸᴸ │\n└───┴──────┘\nLet's say you need to total the values in the\ny\ncolumn:\nSELECT\nsum\n(\ny\n)\nFROM\nt_null_big\n┌─sum(y)─┐\n│      7 │\n└────────┘\nNow you can use the\ngroupArray\nfunction to create an array from the\ny\ncolumn:\nSELECT\ngroupArray\n(\ny\n)\nFROM\nt_null_big\n┌─groupArray(y)─┐\n│ [2,2,3]       │\n└───────────────┘\ngroupArray\ndoes not include\nNULL\nin the resulting array.\nYou can use\nCOALESCE\nto change NULL into a value that makes sense in your use case. For example:\navg(COALESCE(column, 0))\nwith use the column value in the aggregation or zero if NULL:\nSELECT\navg\n(\ny\n)\n,\navg\n(\ncoalesce\n(\ny\n,\n0\n)\n)\nFROM\nt_null_big\n┌─────────────avg(y)─┬─avg(coalesce(y, 0))─┐\n│ 2.3333333333333335 │                 1.4 │\n└────────────────────┴─────────────────────┘\nAlso you can use\nTuple\nto work around NULL skipping behavior. A\nTuple\nthat contains only a\nNULL\nvalue is not\nNULL\n, so the aggregate functions won't skip that row because of that\nNULL\nvalue.\nSELECT\ngroupArray\n(\ny\n)\n,\ngroupArray\n(\ntuple\n(\ny\n)\n)\n.1\nFROM\nt_null_big\n;\n┌─groupArray\n(\ny\n)\n─┬─tupleElement\n(\ngroupArray\n(\ntuple\n(\ny\n)\n)\n,\n1\n)\n─┐\n│\n[\n2\n,\n2\n,\n3\n]\n│\n[\n2\n,\nNULL\n,\n2\n,\n3\n,\nNULL\n]\n│\n└───────────────┴───────────────────────────────────────┘\nNote that aggregations are skipped when the columns are used as arguments to an aggregated function.  For example\ncount\nwithout parameters (\ncount()\n) or with constant ones (\ncount(1)\n) will count all rows in the block (independently of the value of the GROUP BY column as it's not an argument), while\ncount(column)\nwill only return the number of rows where column is not NULL.\nSELECT\nv\n,\ncount\n(\n1\n)\n,\ncount\n(\nv\n)\nFROM\n(\nSELECT\nif\n(\nnumber\n<\n10\n,\nNULL\n,\nnumber\n%\n3\n)\nAS\nv\nFROM\nnumbers\n(\n15\n)\n)\nGROUP\nBY\nv\n┌────v─┬─\ncount\n(\n)\n─┬─\ncount\n(\nv\n)\n─┐\n│ ᴺᵁᴸᴸ │\n10\n│\n0\n│\n│\n0\n│\n1\n│\n1\n│\n│\n1\n│\n2\n│\n2\n│\n│\n2\n│\n2\n│\n2\n│\n└──────┴─────────┴──────────┘\nAnd here is an example of first_value with\nRESPECT NULLS\nwhere we can see that NULL inputs are respected and it will return the first value read, whether it's NULL or not:\nSELECT\ncol\n||\n'_'\n||\n(\n(\ncol\n+\n1\n)\n*\n5\n-\n1\n)\nAS\nrange\n,\nfirst_value\n(\nodd_or_null\n)\nAS\nfirst\n,\nfirst_value\n(\nodd_or_null\n)\nIGNORE\nNULLS\nas\nfirst_ignore_null\n,\nfirst_value\n(\nodd_or_null\n)\nRESPECT NULLS\nas\nfirst_respect_nulls\nFROM\n(\nSELECT\nintDiv\n(\nnumber\n,\n5\n)\nAS\ncol\n,\nif\n(\nnumber\n%\n2\n=\n=\n0\n,\nNULL\n,\nnumber\n)\nAS\nodd_or_null\nFROM\nnumbers\n(\n15\n)\n)\nGROUP\nBY\ncol\nORDER\nBY\ncol\n┌─range─┬─\nfirst\n─┬─first_ignore_null─┬─first_respect_nulls─┐\n│\n0\n_4   │\n1\n│\n1\n│                ᴺᵁᴸᴸ │\n│\n1\n_9   │\n5\n│\n5\n│\n5\n│\n│\n2\n_14  │\n11\n│\n11\n│                ᴺᵁᴸᴸ │\n└───────┴───────┴───────────────────┴─────────────────────┘","attrs_markdown":"[Skip to main content](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions#__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)\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)\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)\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)\n    - [BigQuery](https:\/\/clickhouse.com\/comparison\/bigquery)\n    - [PostgreSQL](https:\/\/clickhouse.com\/comparison\/postgresql)\n    - [Redshift](https:\/\/clickhouse.com\/comparison\/redshift)\n    - [Rockset](https:\/\/clickhouse.com\/comparison\/rockset)\n    - [Snowflake](https:\/\/clickhouse.com\/comparison\/snowflake)\n  - [Video](https:\/\/clickhouse.com\/videos)\n  - [Demo](https:\/\/clickhouse.com\/demos)\n- [Pricing](https:\/\/clickhouse.com\/pricing)\n- [Contact](https:\/\/clickhouse.com\/company\/contact?loc=nav)\n\n[46\\.8k](https:\/\/github.com\/ClickHouse\/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav)\n\n[Search`Ctrl``K`](https:\/\/clickhouse.com\/docs\/search)\n\n[Sign in](https:\/\/console.clickhouse.cloud\/signIn?loc=docs-nav-signIn-cta&glxid=19a5afde-4d87-4da3-ba89-ef87a430ca37&pagePath=%2Fdocs%2Fsql-reference%2Faggregate-functions&origPath=%2Fdocs%2Fsql-reference%2Faggregate-functions&utm_ga=GA1.1.1113250799.1775876986)\n\n[Get started](https:\/\/console.clickhouse.cloud\/signUp?loc=docs-nav-signUp-cta&glxid=19a5afde-4d87-4da3-ba89-ef87a430ca37&pagePath=%2Fdocs%2Fsql-reference%2Faggregate-functions&origPath=%2Fdocs%2Fsql-reference%2Faggregate-functions&utm_ga=GA1.1.1113250799.1775876986)\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)\n\n- [English](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions)\n- [日本語](https:\/\/clickhouse.com\/docs\/jp\/sql-reference\/aggregate-functions)\n- [中文](https:\/\/clickhouse.com\/docs\/zh\/sql-reference\/aggregate-functions)\n- [Русский](https:\/\/clickhouse.com\/docs\/ru\/sql-reference\/aggregate-functions)\n- [한국어](https:\/\/clickhouse.com\/docs\/ko\/sql-reference\/aggregate-functions)\n\n[Skip to main content](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions#__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)\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)\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)\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)\n    - [BigQuery](https:\/\/clickhouse.com\/comparison\/bigquery)\n    - [PostgreSQL](https:\/\/clickhouse.com\/comparison\/postgresql)\n    - [Redshift](https:\/\/clickhouse.com\/comparison\/redshift)\n    - [Rockset](https:\/\/clickhouse.com\/comparison\/rockset)\n    - [Snowflake](https:\/\/clickhouse.com\/comparison\/snowflake)\n  - [Video](https:\/\/clickhouse.com\/videos)\n  - [Demo](https:\/\/clickhouse.com\/demos)\n- [Pricing](https:\/\/clickhouse.com\/pricing)\n- [Contact](https:\/\/clickhouse.com\/company\/contact?loc=nav)\n\n[46\\.8k](https:\/\/github.com\/ClickHouse\/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav)\n\n[Search`Ctrl``K`](https:\/\/clickhouse.com\/docs\/search)\n\n[Sign in](https:\/\/console.clickhouse.cloud\/signIn?loc=docs-nav-signIn-cta&glxid=19a5afde-4d87-4da3-ba89-ef87a430ca37&pagePath=%2Fdocs%2Fsql-reference%2Faggregate-functions&origPath=%2Fdocs%2Fsql-reference%2Faggregate-functions&utm_ga=GA1.1.1113250799.1775876986)\n\n[Get started](https:\/\/console.clickhouse.cloud\/signUp?loc=docs-nav-signUp-cta&glxid=19a5afde-4d87-4da3-ba89-ef87a430ca37&pagePath=%2Fdocs%2Fsql-reference%2Faggregate-functions&origPath=%2Fdocs%2Fsql-reference%2Faggregate-functions&utm_ga=GA1.1.1113250799.1775876986)\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)\n\n- [English](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions)\n- [日本語](https:\/\/clickhouse.com\/docs\/jp\/sql-reference\/aggregate-functions)\n- [中文](https:\/\/clickhouse.com\/docs\/zh\/sql-reference\/aggregate-functions)\n- [Русский](https:\/\/clickhouse.com\/docs\/ru\/sql-reference\/aggregate-functions)\n- [한국어](https:\/\/clickhouse.com\/docs\/ko\/sql-reference\/aggregate-functions)\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)\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\n\n[Edit this page](https:\/\/github.com\/ClickHouse\/ClickHouse\/tree\/master\/docs\/en\/sql-reference\/aggregate-functions\/index.md)\n\n# Aggregate functions\nAggregate functions work in the [normal](http:\/\/www.sql-tutorial.com\/sql-aggregate-functions-sql-tutorial) way as expected by database experts.\n\nClickHouse also supports:\n\n- [Parametric aggregate functions](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/parametric-functions), which accept other parameters in addition to columns.\n- [Combinators](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators), which change the behavior of aggregate functions.\n## NULL processing[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions#null-processing \"Direct link to NULL processing\")\nDuring aggregation, all `NULL` arguments are skipped. If the aggregation has several arguments it will ignore any row in which one or more of them are NULL.\n\nThere is an exception to this rule, which are the functions [`first_value`](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/reference\/first_value), [`last_value`](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/reference\/last_value) and their aliases (`any` and `anyLast` respectively) when followed by the modifier `RESPECT NULLS`. For example, `FIRST_VALUE(b) RESPECT NULLS`.\n\n**Examples:**\n\nConsider this table:\n```\n┌─x─┬────y─┐\n│ 1 │    2 │\n│ 2 │ ᴺᵁᴸᴸ │\n│ 3 │    2 │\n│ 3 │    3 │\n│ 3 │ ᴺᵁᴸᴸ │\n└───┴──────┘\n```\nLet's say you need to total the values in the `y` column:\n```\nSELECT sum(y) FROM t_null_big\n```\n```\n┌─sum(y)─┐\n│      7 │\n└────────┘\n```\nNow you can use the `groupArray` function to create an array from the `y` column:\n```\nSELECT groupArray(y) FROM t_null_big\n```\n```\n┌─groupArray(y)─┐\n│ [2,2,3]       │\n└───────────────┘\n```\n`groupArray` does not include `NULL` in the resulting array.\n\nYou can use [COALESCE](https:\/\/clickhouse.com\/docs\/sql-reference\/functions\/functions-for-nulls#coalesce) to change NULL into a value that makes sense in your use case. For example: `avg(COALESCE(column, 0))` with use the column value in the aggregation or zero if NULL:\n```\nSELECT\n    avg(y),\n    avg(coalesce(y, 0))\nFROM t_null_big\n```\n```\n┌─────────────avg(y)─┬─avg(coalesce(y, 0))─┐\n│ 2.3333333333333335 │                 1.4 │\n└────────────────────┴─────────────────────┘\n```\nAlso you can use [Tuple](https:\/\/clickhouse.com\/docs\/sql-reference\/data-types\/tuple) to work around NULL skipping behavior. A `Tuple` that contains only a `NULL` value is not `NULL`, so the aggregate functions won't skip that row because of that `NULL` value.\n```\nSELECT\n    groupArray(y),\n    groupArray(tuple(y)).1\nFROM t_null_big;\n\n┌─groupArray(y)─┬─tupleElement(groupArray(tuple(y)), 1)─┐\n│ [2,2,3]       │ [2,NULL,2,3,NULL]                     │\n└───────────────┴───────────────────────────────────────┘\n```\nNote that aggregations are skipped when the columns are used as arguments to an aggregated function. For example [`count`](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/reference\/count) without parameters (`count()`) or with constant ones (`count(1)`) will count all rows in the block (independently of the value of the GROUP BY column as it's not an argument), while `count(column)` will only return the number of rows where column is not NULL.\n```\nSELECT\n    v,\n    count(1),\n    count(v)\nFROM\n(\n    SELECT if(number < 10, NULL, number % 3) AS v\n    FROM numbers(15)\n)\nGROUP BY v\n\n┌────v─┬─count()─┬─count(v)─┐\n│ ᴺᵁᴸᴸ │      10 │        0 │\n│    0 │       1 │        1 │\n│    1 │       2 │        2 │\n│    2 │       2 │        2 │\n└──────┴─────────┴──────────┘\n```\nAnd here is an example of first\\_value with `RESPECT NULLS` where we can see that NULL inputs are respected and it will return the first value read, whether it's NULL or not:\n```\nSELECT\n    col || '_' || ((col + 1) * 5 - 1) AS range,\n    first_value(odd_or_null) AS first,\n    first_value(odd_or_null) IGNORE NULLS as first_ignore_null,\n    first_value(odd_or_null) RESPECT NULLS as first_respect_nulls\nFROM\n(\n    SELECT\n        intDiv(number, 5) AS col,\n        if(number % 2 == 0, NULL, number) AS odd_or_null\n    FROM numbers(15)\n)\nGROUP BY col\nORDER BY col\n\n┌─range─┬─first─┬─first_ignore_null─┬─first_respect_nulls─┐\n│ 0_4   │     1 │                 1 │                ᴺᵁᴸᴸ │\n│ 1_9   │     5 │                 5 │                   5 │\n│ 2_14  │    11 │                11 │                ᴺᵁᴸᴸ │\n└───────┴───────┴───────────────────┴─────────────────────┘\n```\n\n[Previous WebAssembly UDFs](https:\/\/clickhouse.com\/docs\/sql-reference\/functions\/wasm_udf)\n\n[Next Aggregate Functions](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/reference)\n\n- [NULL processing](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions#null-processing)\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=19a5afde-4d87-4da3-ba89-ef87a430ca37&pagePath=%2Fdocs%2Fsql-reference%2Faggregate-functions&origPath=%2Fdocs%2Fsql-reference%2Faggregate-functions&utm_ga=GA1.1.1113250799.1775876986)\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- Formats▼\n- [Data Lakes](https:\/\/clickhouse.com\/docs\/sql-reference\/datalakes)","attrs_readable_markdown":"Aggregate functions work in the [normal](http:\/\/www.sql-tutorial.com\/sql-aggregate-functions-sql-tutorial) way as expected by database experts.\n\nClickHouse also supports:\n\n- [Parametric aggregate functions](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/parametric-functions), which accept other parameters in addition to columns.\n- [Combinators](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/combinators), which change the behavior of aggregate functions.\n## NULL processing[](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions#null-processing \"Direct link to NULL processing\")\nDuring aggregation, all `NULL` arguments are skipped. If the aggregation has several arguments it will ignore any row in which one or more of them are NULL.\n\nThere is an exception to this rule, which are the functions [`first_value`](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/reference\/first_value), [`last_value`](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/reference\/last_value) and their aliases (`any` and `anyLast` respectively) when followed by the modifier `RESPECT NULLS`. For example, `FIRST_VALUE(b) RESPECT NULLS`.\n\n**Examples:**\n\nConsider this table:\n```\n┌─x─┬────y─┐\n│ 1 │    2 │\n│ 2 │ ᴺᵁᴸᴸ │\n│ 3 │    2 │\n│ 3 │    3 │\n│ 3 │ ᴺᵁᴸᴸ │\n└───┴──────┘\n```\nLet's say you need to total the values in the `y` column:\n```\nSELECT sum(y) FROM t_null_big\n```\n```\n┌─sum(y)─┐\n│      7 │\n└────────┘\n```\nNow you can use the `groupArray` function to create an array from the `y` column:\n```\nSELECT groupArray(y) FROM t_null_big\n```\n```\n┌─groupArray(y)─┐\n│ [2,2,3]       │\n└───────────────┘\n```\n`groupArray` does not include `NULL` in the resulting array.\n\nYou can use [COALESCE](https:\/\/clickhouse.com\/docs\/sql-reference\/functions\/functions-for-nulls#coalesce) to change NULL into a value that makes sense in your use case. For example: `avg(COALESCE(column, 0))` with use the column value in the aggregation or zero if NULL:\n```\nSELECT\n    avg(y),\n    avg(coalesce(y, 0))\nFROM t_null_big\n```\n```\n┌─────────────avg(y)─┬─avg(coalesce(y, 0))─┐\n│ 2.3333333333333335 │                 1.4 │\n└────────────────────┴─────────────────────┘\n```\nAlso you can use [Tuple](https:\/\/clickhouse.com\/docs\/sql-reference\/data-types\/tuple) to work around NULL skipping behavior. A `Tuple` that contains only a `NULL` value is not `NULL`, so the aggregate functions won't skip that row because of that `NULL` value.\n```\nSELECT\n    groupArray(y),\n    groupArray(tuple(y)).1\nFROM t_null_big;\n\n┌─groupArray(y)─┬─tupleElement(groupArray(tuple(y)), 1)─┐\n│ [2,2,3]       │ [2,NULL,2,3,NULL]                     │\n└───────────────┴───────────────────────────────────────┘\n```\nNote that aggregations are skipped when the columns are used as arguments to an aggregated function. For example [`count`](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions\/reference\/count) without parameters (`count()`) or with constant ones (`count(1)`) will count all rows in the block (independently of the value of the GROUP BY column as it's not an argument), while `count(column)` will only return the number of rows where column is not NULL.\n```\nSELECT\n    v,\n    count(1),\n    count(v)\nFROM\n(\n    SELECT if(number < 10, NULL, number % 3) AS v\n    FROM numbers(15)\n)\nGROUP BY v\n\n┌────v─┬─count()─┬─count(v)─┐\n│ ᴺᵁᴸᴸ │      10 │        0 │\n│    0 │       1 │        1 │\n│    1 │       2 │        2 │\n│    2 │       2 │        2 │\n└──────┴─────────┴──────────┘\n```\nAnd here is an example of first\\_value with `RESPECT NULLS` where we can see that NULL inputs are respected and it will return the first value read, whether it's NULL or not:\n```\nSELECT\n    col || '_' || ((col + 1) * 5 - 1) AS range,\n    first_value(odd_or_null) AS first,\n    first_value(odd_or_null) IGNORE NULLS as first_ignore_null,\n    first_value(odd_or_null) RESPECT NULLS as first_respect_nulls\nFROM\n(\n    SELECT\n        intDiv(number, 5) AS col,\n        if(number % 2 == 0, NULL, number) AS odd_or_null\n    FROM numbers(15)\n)\nGROUP BY col\nORDER BY col\n\n┌─range─┬─first─┬─first_ignore_null─┬─first_respect_nulls─┐\n│ 0_4   │     1 │                 1 │                ᴺᵁᴸᴸ │\n│ 1_9   │     5 │                 5 │                   5 │\n│ 2_14  │    11 │                11 │                ᴺᵁᴸᴸ │\n└───────┴───────┴───────────────────┴─────────────────────┘\n```","meta_canonical":null}

3. Robots.txt Check

Query:

Response:

4. Spam/Ban Check

Query:

Response:

5. Seen Status Check

ℹ️ Skipped - page is already crawled

📄

INDEXABLE

✅

CRAWLED

11 hours ago

🤖

ROBOTS ALLOWED

Page Info Filters

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

Page Details

Property	Value
URL	https://clickhouse.com/docs/sql-reference/aggregate-functions
Last Crawled	2026-04-11 03:09:40 (11 hours ago)
First Indexed	2025-02-21 00:33:21 (1 year ago)
HTTP Status Code	200
Meta Title	Aggregate Functions \| ClickHouse Docs
Meta Description	Documentation for Aggregate Functions
Meta Canonical	null
Boilerpipe Text	Aggregate functions work in the normal way as expected by database experts. ClickHouse also supports: Parametric aggregate functions , which accept other parameters in addition to columns. Combinators , which change the behavior of aggregate functions. NULL processing During aggregation, all NULL arguments are skipped. If the aggregation has several arguments it will ignore any row in which one or more of them are NULL. There is an exception to this rule, which are the functions first_value , last_value and their aliases ( any and anyLast respectively) when followed by the modifier RESPECT NULLS . For example, FIRST_VALUE(b) RESPECT NULLS . Examples: Consider this table: ┌─x─┬────y─┐ │ 1 │ 2 │ │ 2 │ ᴺᵁᴸᴸ │ │ 3 │ 2 │ │ 3 │ 3 │ │ 3 │ ᴺᵁᴸᴸ │ └───┴──────┘ Let's say you need to total the values in the y column: SELECT sum ( y ) FROM t_null_big ┌─sum(y)─┐ │ 7 │ └────────┘ Now you can use the groupArray function to create an array from the y column: SELECT groupArray ( y ) FROM t_null_big ┌─groupArray(y)─┐ │ [2,2,3] │ └───────────────┘ groupArray does not include NULL in the resulting array. You can use COALESCE to change NULL into a value that makes sense in your use case. For example: avg(COALESCE(column, 0)) with use the column value in the aggregation or zero if NULL: SELECT avg ( y ) , avg ( coalesce ( y , 0 ) ) FROM t_null_big ┌─────────────avg(y)─┬─avg(coalesce(y, 0))─┐ │ 2.3333333333333335 │ 1.4 │ └────────────────────┴─────────────────────┘ Also you can use Tuple to work around NULL skipping behavior. A Tuple that contains only a NULL value is not NULL , so the aggregate functions won't skip that row because of that NULL value. SELECT groupArray ( y ) , groupArray ( tuple ( y ) ) .1 FROM t_null_big ; ┌─groupArray ( y ) ─┬─tupleElement ( groupArray ( tuple ( y ) ) , 1 ) ─┐ │ [ 2 , 2 , 3 ] │ [ 2 , NULL , 2 , 3 , NULL ] │ └───────────────┴───────────────────────────────────────┘ Note that aggregations are skipped when the columns are used as arguments to an aggregated function. For example count without parameters ( count() ) or with constant ones ( count(1) ) will count all rows in the block (independently of the value of the GROUP BY column as it's not an argument), while count(column) will only return the number of rows where column is not NULL. SELECT v , count ( 1 ) , count ( v ) FROM ( SELECT if ( number < 10 , NULL , number % 3 ) AS v FROM numbers ( 15 ) ) GROUP BY v ┌────v─┬─ count ( ) ─┬─ count ( v ) ─┐ │ ᴺᵁᴸᴸ │ 10 │ 0 │ │ 0 │ 1 │ 1 │ │ 1 │ 2 │ 2 │ │ 2 │ 2 │ 2 │ └──────┴─────────┴──────────┘ And here is an example of first_value with RESPECT NULLS where we can see that NULL inputs are respected and it will return the first value read, whether it's NULL or not: SELECT col \|\| '_' \|\| ( ( col + 1 ) * 5 - 1 ) AS range , first_value ( odd_or_null ) AS first , first_value ( odd_or_null ) IGNORE NULLS as first_ignore_null , first_value ( odd_or_null ) RESPECT NULLS as first_respect_nulls FROM ( SELECT intDiv ( number , 5 ) AS col , if ( number % 2 = = 0 , NULL , number ) AS odd_or_null FROM numbers ( 15 ) ) GROUP BY col ORDER BY col ┌─range─┬─ first ─┬─first_ignore_null─┬─first_respect_nulls─┐ │ 0 _4 │ 1 │ 1 │ ᴺᵁᴸᴸ │ │ 1 _9 │ 5 │ 5 │ 5 │ │ 2 _14 │ 11 │ 11 │ ᴺᵁᴸᴸ │ └───────┴───────┴───────────────────┴─────────────────────┘
Markdown	[Skip to main content](https://clickhouse.com/docs/sql-reference/aggregate-functions#__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) - [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) - [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) - [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) - [BigQuery](https://clickhouse.com/comparison/bigquery) - [PostgreSQL](https://clickhouse.com/comparison/postgresql) - [Redshift](https://clickhouse.com/comparison/redshift) - [Rockset](https://clickhouse.com/comparison/rockset) - [Snowflake](https://clickhouse.com/comparison/snowflake) - [Video](https://clickhouse.com/videos) - [Demo](https://clickhouse.com/demos) - [Pricing](https://clickhouse.com/pricing) - [Contact](https://clickhouse.com/company/contact?loc=nav) [46\.8k](https://github.com/ClickHouse/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav) [Search`Ctrl``K`](https://clickhouse.com/docs/search) [Sign in](https://console.clickhouse.cloud/signIn?loc=docs-nav-signIn-cta&glxid=19a5afde-4d87-4da3-ba89-ef87a430ca37&pagePath=%2Fdocs%2Fsql-reference%2Faggregate-functions&origPath=%2Fdocs%2Fsql-reference%2Faggregate-functions&utm_ga=GA1.1.1113250799.1775876986) [Get started](https://console.clickhouse.cloud/signUp?loc=docs-nav-signUp-cta&glxid=19a5afde-4d87-4da3-ba89-ef87a430ca37&pagePath=%2Fdocs%2Fsql-reference%2Faggregate-functions&origPath=%2Fdocs%2Fsql-reference%2Faggregate-functions&utm_ga=GA1.1.1113250799.1775876986) [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) - [English](https://clickhouse.com/docs/sql-reference/aggregate-functions) - [日本語](https://clickhouse.com/docs/jp/sql-reference/aggregate-functions) - [中文](https://clickhouse.com/docs/zh/sql-reference/aggregate-functions) - [Русский](https://clickhouse.com/docs/ru/sql-reference/aggregate-functions) - [한국어](https://clickhouse.com/docs/ko/sql-reference/aggregate-functions) [Skip to main content](https://clickhouse.com/docs/sql-reference/aggregate-functions#__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) - [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) - [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) - [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) - [BigQuery](https://clickhouse.com/comparison/bigquery) - [PostgreSQL](https://clickhouse.com/comparison/postgresql) - [Redshift](https://clickhouse.com/comparison/redshift) - [Rockset](https://clickhouse.com/comparison/rockset) - [Snowflake](https://clickhouse.com/comparison/snowflake) - [Video](https://clickhouse.com/videos) - [Demo](https://clickhouse.com/demos) - [Pricing](https://clickhouse.com/pricing) - [Contact](https://clickhouse.com/company/contact?loc=nav) [46\.8k](https://github.com/ClickHouse/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav) [Search`Ctrl``K`](https://clickhouse.com/docs/search) [Sign in](https://console.clickhouse.cloud/signIn?loc=docs-nav-signIn-cta&glxid=19a5afde-4d87-4da3-ba89-ef87a430ca37&pagePath=%2Fdocs%2Fsql-reference%2Faggregate-functions&origPath=%2Fdocs%2Fsql-reference%2Faggregate-functions&utm_ga=GA1.1.1113250799.1775876986) [Get started](https://console.clickhouse.cloud/signUp?loc=docs-nav-signUp-cta&glxid=19a5afde-4d87-4da3-ba89-ef87a430ca37&pagePath=%2Fdocs%2Fsql-reference%2Faggregate-functions&origPath=%2Fdocs%2Fsql-reference%2Faggregate-functions&utm_ga=GA1.1.1113250799.1775876986) [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) - [English](https://clickhouse.com/docs/sql-reference/aggregate-functions) - [日本語](https://clickhouse.com/docs/jp/sql-reference/aggregate-functions) - [中文](https://clickhouse.com/docs/zh/sql-reference/aggregate-functions) - [Русский](https://clickhouse.com/docs/ru/sql-reference/aggregate-functions) - [한국어](https://clickhouse.com/docs/ko/sql-reference/aggregate-functions) [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) - [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 [Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/aggregate-functions/index.md) # Aggregate functions Aggregate functions work in the [normal](http://www.sql-tutorial.com/sql-aggregate-functions-sql-tutorial) way as expected by database experts. ClickHouse also supports: - [Parametric aggregate functions](https://clickhouse.com/docs/sql-reference/aggregate-functions/parametric-functions), which accept other parameters in addition to columns. - [Combinators](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators), which change the behavior of aggregate functions. ## NULL processing[](https://clickhouse.com/docs/sql-reference/aggregate-functions#null-processing "Direct link to NULL processing") During aggregation, all `NULL` arguments are skipped. If the aggregation has several arguments it will ignore any row in which one or more of them are NULL. There is an exception to this rule, which are the functions [`first_value`](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/first_value), [`last_value`](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/last_value) and their aliases (`any` and `anyLast` respectively) when followed by the modifier `RESPECT NULLS`. For example, `FIRST_VALUE(b) RESPECT NULLS`. Examples: Consider this table: ``` ┌─x─┬────y─┐ │ 1 │ 2 │ │ 2 │ ᴺᵁᴸᴸ │ │ 3 │ 2 │ │ 3 │ 3 │ │ 3 │ ᴺᵁᴸᴸ │ └───┴──────┘ ``` Let's say you need to total the values in the `y` column: ``` SELECT sum(y) FROM t_null_big ``` ``` ┌─sum(y)─┐ │ 7 │ └────────┘ ``` Now you can use the `groupArray` function to create an array from the `y` column: ``` SELECT groupArray(y) FROM t_null_big ``` ``` ┌─groupArray(y)─┐ │ [2,2,3] │ └───────────────┘ ``` `groupArray` does not include `NULL` in the resulting array. You can use [COALESCE](https://clickhouse.com/docs/sql-reference/functions/functions-for-nulls#coalesce) to change NULL into a value that makes sense in your use case. For example: `avg(COALESCE(column, 0))` with use the column value in the aggregation or zero if NULL: ``` SELECT avg(y), avg(coalesce(y, 0)) FROM t_null_big ``` ``` ┌─────────────avg(y)─┬─avg(coalesce(y, 0))─┐ │ 2.3333333333333335 │ 1.4 │ └────────────────────┴─────────────────────┘ ``` Also you can use [Tuple](https://clickhouse.com/docs/sql-reference/data-types/tuple) to work around NULL skipping behavior. A `Tuple` that contains only a `NULL` value is not `NULL`, so the aggregate functions won't skip that row because of that `NULL` value. ``` SELECT groupArray(y), groupArray(tuple(y)).1 FROM t_null_big; ┌─groupArray(y)─┬─tupleElement(groupArray(tuple(y)), 1)─┐ │ [2,2,3] │ [2,NULL,2,3,NULL] │ └───────────────┴───────────────────────────────────────┘ ``` Note that aggregations are skipped when the columns are used as arguments to an aggregated function. For example [`count`](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/count) without parameters (`count()`) or with constant ones (`count(1)`) will count all rows in the block (independently of the value of the GROUP BY column as it's not an argument), while `count(column)` will only return the number of rows where column is not NULL. ``` SELECT v, count(1), count(v) FROM ( SELECT if(number < 10, NULL, number % 3) AS v FROM numbers(15) ) GROUP BY v ┌────v─┬─count()─┬─count(v)─┐ │ ᴺᵁᴸᴸ │ 10 │ 0 │ │ 0 │ 1 │ 1 │ │ 1 │ 2 │ 2 │ │ 2 │ 2 │ 2 │ └──────┴─────────┴──────────┘ ``` And here is an example of first\_value with `RESPECT NULLS` where we can see that NULL inputs are respected and it will return the first value read, whether it's NULL or not: ``` SELECT col \|\| '_' \|\| ((col + 1) * 5 - 1) AS range, first_value(odd_or_null) AS first, first_value(odd_or_null) IGNORE NULLS as first_ignore_null, first_value(odd_or_null) RESPECT NULLS as first_respect_nulls FROM ( SELECT intDiv(number, 5) AS col, if(number % 2 == 0, NULL, number) AS odd_or_null FROM numbers(15) ) GROUP BY col ORDER BY col ┌─range─┬─first─┬─first_ignore_null─┬─first_respect_nulls─┐ │ 0_4 │ 1 │ 1 │ ᴺᵁᴸᴸ │ │ 1_9 │ 5 │ 5 │ 5 │ │ 2_14 │ 11 │ 11 │ ᴺᵁᴸᴸ │ └───────┴───────┴───────────────────┴─────────────────────┘ ``` [Previous WebAssembly UDFs](https://clickhouse.com/docs/sql-reference/functions/wasm_udf) [Next Aggregate Functions](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference) - [NULL processing](https://clickhouse.com/docs/sql-reference/aggregate-functions#null-processing) 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=19a5afde-4d87-4da3-ba89-ef87a430ca37&pagePath=%2Fdocs%2Fsql-reference%2Faggregate-functions&origPath=%2Fdocs%2Fsql-reference%2Faggregate-functions&utm_ga=GA1.1.1113250799.1775876986) © 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▼ - Formats▼ - [Data Lakes](https://clickhouse.com/docs/sql-reference/datalakes)
Readable Markdown	Aggregate functions work in the [normal](http://www.sql-tutorial.com/sql-aggregate-functions-sql-tutorial) way as expected by database experts. ClickHouse also supports: - [Parametric aggregate functions](https://clickhouse.com/docs/sql-reference/aggregate-functions/parametric-functions), which accept other parameters in addition to columns. - [Combinators](https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators), which change the behavior of aggregate functions. ## NULL processing[](https://clickhouse.com/docs/sql-reference/aggregate-functions#null-processing "Direct link to NULL processing") During aggregation, all `NULL` arguments are skipped. If the aggregation has several arguments it will ignore any row in which one or more of them are NULL. There is an exception to this rule, which are the functions [`first_value`](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/first_value), [`last_value`](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/last_value) and their aliases (`any` and `anyLast` respectively) when followed by the modifier `RESPECT NULLS`. For example, `FIRST_VALUE(b) RESPECT NULLS`. Examples: Consider this table: ``` ┌─x─┬────y─┐ │ 1 │ 2 │ │ 2 │ ᴺᵁᴸᴸ │ │ 3 │ 2 │ │ 3 │ 3 │ │ 3 │ ᴺᵁᴸᴸ │ └───┴──────┘ ``` Let's say you need to total the values in the `y` column: ``` SELECT sum(y) FROM t_null_big ``` ``` ┌─sum(y)─┐ │ 7 │ └────────┘ ``` Now you can use the `groupArray` function to create an array from the `y` column: ``` SELECT groupArray(y) FROM t_null_big ``` ``` ┌─groupArray(y)─┐ │ [2,2,3] │ └───────────────┘ ``` `groupArray` does not include `NULL` in the resulting array. You can use [COALESCE](https://clickhouse.com/docs/sql-reference/functions/functions-for-nulls#coalesce) to change NULL into a value that makes sense in your use case. For example: `avg(COALESCE(column, 0))` with use the column value in the aggregation or zero if NULL: ``` SELECT avg(y), avg(coalesce(y, 0)) FROM t_null_big ``` ``` ┌─────────────avg(y)─┬─avg(coalesce(y, 0))─┐ │ 2.3333333333333335 │ 1.4 │ └────────────────────┴─────────────────────┘ ``` Also you can use [Tuple](https://clickhouse.com/docs/sql-reference/data-types/tuple) to work around NULL skipping behavior. A `Tuple` that contains only a `NULL` value is not `NULL`, so the aggregate functions won't skip that row because of that `NULL` value. ``` SELECT groupArray(y), groupArray(tuple(y)).1 FROM t_null_big; ┌─groupArray(y)─┬─tupleElement(groupArray(tuple(y)), 1)─┐ │ [2,2,3] │ [2,NULL,2,3,NULL] │ └───────────────┴───────────────────────────────────────┘ ``` Note that aggregations are skipped when the columns are used as arguments to an aggregated function. For example [`count`](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/count) without parameters (`count()`) or with constant ones (`count(1)`) will count all rows in the block (independently of the value of the GROUP BY column as it's not an argument), while `count(column)` will only return the number of rows where column is not NULL. ``` SELECT v, count(1), count(v) FROM ( SELECT if(number < 10, NULL, number % 3) AS v FROM numbers(15) ) GROUP BY v ┌────v─┬─count()─┬─count(v)─┐ │ ᴺᵁᴸᴸ │ 10 │ 0 │ │ 0 │ 1 │ 1 │ │ 1 │ 2 │ 2 │ │ 2 │ 2 │ 2 │ └──────┴─────────┴──────────┘ ``` And here is an example of first\_value with `RESPECT NULLS` where we can see that NULL inputs are respected and it will return the first value read, whether it's NULL or not: ``` SELECT col \|\| '_' \|\| ((col + 1) * 5 - 1) AS range, first_value(odd_or_null) AS first, first_value(odd_or_null) IGNORE NULLS as first_ignore_null, first_value(odd_or_null) RESPECT NULLS as first_respect_nulls FROM ( SELECT intDiv(number, 5) AS col, if(number % 2 == 0, NULL, number) AS odd_or_null FROM numbers(15) ) GROUP BY col ORDER BY col ┌─range─┬─first─┬─first_ignore_null─┬─first_respect_nulls─┐ │ 0_4 │ 1 │ 1 │ ᴺᵁᴸᴸ │ │ 1_9 │ 5 │ 5 │ 5 │ │ 2_14 │ 11 │ 11 │ ᴺᵁᴸᴸ │ └───────┴───────┴───────────────────┴─────────────────────┘ ```
Shard	89 (laksa)
Root Hash	12633450985039531489
Unparsed URL	com,clickhouse!/docs/sql-reference/aggregate-functions s443