Edit

# count

## Description

Returns the total number of rows specified by an expression.

This function has three variations:

• `COUNT(*)` counts all rows in a table, no matter whether they contain NULL values.

• `COUNT(expr)` counts the number of rows with non-NULL values in a specific column.

• `COUNT(DISTINCT expr)` counts the number of distinct non-NULL values in a column.

`COUNT(DISTINCT expr)` is used for exact count distinct. If you require higher count distinct performance, see Use bitmap for exact count discount.

From StarRocks 2.4 onwards, you can use multiple COUNT(DISTINCT) in one statement.

## Syntax

``````COUNT(expr)
COUNT(DISTINCT expr [,expr,...])```````

## Parameters

`expr`: the column or expression based on which `count()` is performed. If `expr` is a column name, the column can be of any data type.

## Return value

Returns a numeric value. If no rows can be found, 0 is returned. This function ignores NULL values.

## Examples

Suppose there is a table named `test`. Query the country, category, and supplier of each order by `id`.

``````select * from test order by id;
+------+----------+----------+------------+
| id   | country  | category | supplier   |
+------+----------+----------+------------+
| 1001 | US       | A        | supplier_1 |
| 1002 | Thailand | A        | supplier_2 |
| 1003 | Turkey   | B        | supplier_3 |
| 1004 | US       | A        | supplier_2 |
| 1005 | China    | C        | supplier_4 |
| 1006 | Japan    | D        | supplier_3 |
| 1007 | Japan    | NULL     | supplier_5 |
+------+----------+----------+------------+``````

Example 1：Count the number of rows in table `test`.

``````    select count(*) from test;
+----------+
| count(*) |
+----------+
|        7 |
+----------+``````

Example 2：Count the number of values in the `id` column.

``````    select count(id) from test;
+-----------+
| count(id) |
+-----------+
|         7 |
+-----------+``````

Example 3: Count the number of values in the `category` column while ignoring NULL values.

``````select count(category) from test;
+-----------------+
| count(category) |
+-----------------+
|         6       |
+-----------------+``````

Example 4：Count the number of distinct values in the `category` column.

``````select count(distinct category) from test;
+-------------------------+
| count(DISTINCT category) |
+-------------------------+
|                       4 |
+-------------------------+``````

Example 5：Count the number of combinations that can be formed by `category` and `supplier`.

``````select count(distinct category, supplier) from test;
+------------------------------------+
| count(DISTINCT category, supplier) |
+------------------------------------+
|                                  5 |
+------------------------------------+``````

In the output, the combination with `id` 1004 duplicates with the combination with `id` 1002. They are counted only once. The combination with `id` 1007 has a NULL value and is not counted.

Example 6: Use multiple COUNT(DISTINCT) in one statement.

``````select count(distinct country, category), count(distinct country,supplier) from test;
+-----------------------------------+-----------------------------------+
| count(DISTINCT country, category) | count(DISTINCT country, supplier) |
+-----------------------------------+-----------------------------------+
|                                 6 |                                 7 |
+-----------------------------------+-----------------------------------+``````
Edit
{"ft_lang_label":"__label__en","ft_lang_prob":0.5014211,"math_prob":0.8510243,"size":7808,"snap":"2023-14-2023-23","text_gpt3_token_len":2021,"char_repetition_ratio":0.20002563,"word_repetition_ratio":0.012648221,"special_character_ratio":0.32108095,"punctuation_ratio":0.056,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.97089773,"pos_list":[0],"im_url_duplicate_count":[null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2023-03-26T08:03:36Z\",\"WARC-Record-ID\":\"<urn:uuid:f4852b9e-4958-4d42-9068-6d939d6b4c4c>\",\"Content-Length\":\"137875\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:cecc3a7e-d577-4c50-912e-26b7d6c63789>\",\"WARC-Concurrent-To\":\"<urn:uuid:fa4cd0ce-beca-4046-a135-eaa7976788c7>\",\"WARC-IP-Address\":\"18.67.76.66\",\"WARC-Target-URI\":\"https://docs.starrocks.io/en-us/2.4/sql-reference/sql-functions/aggregate-functions/count\",\"WARC-Payload-Digest\":\"sha1:OCYAPXQHOSVUWTAFUZ2YSA66NGSEFHMH\",\"WARC-Block-Digest\":\"sha1:ZI7NM3JZK5DIBC2W4J6THHYAVBSW4SVC\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2023/CC-MAIN-2023-14/CC-MAIN-2023-14_segments_1679296945440.67_warc_CC-MAIN-20230326075911-20230326105911-00415.warc.gz\"}"}