COUNT

Description

The COUNT function is used to return the number of rows in a set of data. It can count the total number of rows, the number of non-NULL values in a specified column, or the number of distinct values in a specified column.

Syntax

COUNT(*)
COUNT([DISTINCT] expr1[, expr2, ...])

Parameters

• exprN: An expression of any type.

Return Results

• The return value type is bigint.
• When using the COUNT(*) form, all rows are counted, including those with NULL values.
• When using the COUNT(expr1[, expr2, ...]) form, if any column in a row is NULL, that row is ignored.
• When using the COUNT(DISTINCT expr1[, expr2, ...]) form, the specified columns are first deduplicated, and then the number of non-NULL values is counted.

Examples

1. Count the number of all rows (including those with NULL values):

   > SELECT COUNT(*) FROM VALUES (null), (1), (3), (4) AS tab(col);
   4
   

2. Count the number of non-NULL values in a specified column:

   > SELECT COUNT(a, b) FROM VALUES (null, null), (1, null), (null, 3), (4, 5) AS tab(a, b);
   1
   

3. Count the number of distinct values in a specified column (ignoring NULL values):

   > SELECT COUNT(DISTINCT a, b) FROM VALUES (1, null), (1, null), (4, 5), (4, 5), (1, 2) AS tab(a, b);
   2
   

4. Count the number of specific values in a column:

   > SELECT COUNT(*) FROM customers WHERE status = 'active';
   150
   

5. Count the number of sales for different products in the sales records (ignore NULL values):

   > SELECT COUNT(DISTINCT product_id) FROM sales_records;
   50