# APPROX_COUNT_DISTINCT (Transact-SQL)

Applies to: SQL Server 2019 (15.x) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics

This function returns the approximate number of unique non-null values in a group. Transact-SQL Syntax Conventions

## Syntax

``````APPROX_COUNT_DISTINCT ( expression )
``````

Note

To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

## Arguments

expression
An expression of any type, except image, sql_variant, ntext, or text.

bigint

## Remarks

`APPROX_COUNT_DISTINCT( expression )` evaluates an expression for each row in a group, and returns the approximate number of unique non-null values in a group. This function is designed to provide aggregations across large data sets where responsiveness is more critical than absolute precision.

`APPROX_COUNT_DISTINCT` is designed for use in big data scenarios and is optimized for the following conditions:

• Access of data sets that are millions of rows or higher and
• Aggregation of a column or columns that have many distinct values

The function implementation guarantees up to a 2% error rate within a 97% probability.

`APPROX_COUNT_DISTINCT` requires less memory than an exhaustive COUNT DISTINCT operation. Given the smaller memory footprint, `APPROX_COUNT_DISTINCT` is less likely to spill memory to disk compared to a precise COUNT DISTINCT operation. To learn more about the algorithm used to achieve this, see HyperLogLog.

Note

With collation sensitive strings, APPROX_COUNT_DISTINCT uses a binary match and provides results that would have been generated in the presence of BIN collations and not BIN2.

## Examples

### A. Using APPROX_COUNT_DISTINCT

This example returns the approximate number of different order keys from the orders table.

``````SELECT APPROX_COUNT_DISTINCT(O_OrderKey) AS Approx_Distinct_OrderKey
FROM dbo.Orders;
``````

Here is the result set.

``````Approx_Distinct_OrderKey
------------------------
15164704
``````

### B. Using APPROX_COUNT_DISTINCT with GROUP BY

This example returns the approximate number of different order keys by order status from the orders table.

``````SELECT O_OrderStatus, APPROX_COUNT_DISTINCT(O_OrderKey) AS Approx_Distinct_OrderKey
FROM dbo.Orders
GROUP BY O_OrderStatus
ORDER BY O_OrderStatus;
``````

Here is the result set.

``````O_OrderStatus                                                    Approx_Distinct_OrderKey
---------------------------------------------------------------- ------------------------
F                                                                7397838
O                                                                7387803
P                                                                388036
``````
{"ft_lang_label":"__label__en","ft_lang_prob":0.74261266,"math_prob":0.73280114,"size":2409,"snap":"2021-43-2021-49","text_gpt3_token_len":514,"char_repetition_ratio":0.17879418,"word_repetition_ratio":0.0952381,"special_character_ratio":0.23370694,"punctuation_ratio":0.09243698,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.97885823,"pos_list":[0,1,2,3,4,5,6,7,8,9,10],"im_url_duplicate_count":[null,null,null,null,null,null,null,null,null,null,null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2021-12-01T01:58:29Z\",\"WARC-Record-ID\":\"<urn:uuid:b05edcf6-20cd-4055-bcd5-3a071c7d62dd>\",\"Content-Length\":\"41994\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:637b4e18-6774-4dea-b064-4a7ecdf03e9e>\",\"WARC-Concurrent-To\":\"<urn:uuid:dbc109a8-ff0e-43ae-b9ee-ffe44efd62cd>\",\"WARC-IP-Address\":\"23.39.179.42\",\"WARC-Target-URI\":\"https://docs.microsoft.com/en-us/sql/t-sql/functions/approx-count-distinct-transact-sql?view=sql-server-ver15&viewFallbackFrom=sql-server-%20ver15\",\"WARC-Payload-Digest\":\"sha1:Z5GPAXMPZLBKHFUI3APV6FFABZ53MDSL\",\"WARC-Block-Digest\":\"sha1:Z6BNEXFQDQS2KP6XY3N4ZOH5LALRKA4Z\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2021/CC-MAIN-2021-49/CC-MAIN-2021-49_segments_1637964359082.76_warc_CC-MAIN-20211130232232-20211201022232-00540.warc.gz\"}"}