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 |
+-----------------------------------+-----------------------------------+