COUNT¶

Counts the number of rows or non-NULL values in a group.

Syntax¶

Parameters¶

The function accepts two forms:

COUNT(*): Counts all rows in the group, including rows with NULL values.
COUNT(expression): Counts only the rows where expression is not NULL.

Returns¶

Returns a BIGINT representing the count of rows or non-NULL values. Returns 0 if the input set is empty or if all expression values are NULL (for COUNT(expression)).

Examples¶

Setup¶

For these examples, assume we have a sales table:

CREATE TABLE sales(
    id BIGINT,
    product STRING,
    region STRING,
    amount BIGINT,
    PRIMARY KEY(id))

INSERT INTO sales VALUES
    (1, 'Widget', 'North', 100),
    (2, 'Widget', 'South', 150),
    (3, 'Gadget', 'North', 200),
    (4, 'Gadget', 'South', NULL),
    (5, 'Widget', 'North', 120)

COUNT(*) - Count All Rows¶

Count all rows in the table:

SELECT COUNT(*) AS total_sales FROM sales

`total_sales`
`5`

COUNT(column) - Count Non-NULL Values¶

Count only non-NULL amounts:

SELECT COUNT(amount) AS sales_with_amount FROM sales

`sales_with_amount`
`4`

Notice that the count is 4, not 5, because the fourth row has a NULL amount.

COUNT with GROUP BY¶

Count sales per product:

SELECT product, COUNT(*) AS sales_count
FROM sales
GROUP BY product

`product`	`sales_count`
`"Widget"`	`3`
`"Gadget"`	`2`

Count non-NULL amounts per region:

SELECT region, COUNT(amount) AS non_null_amounts
FROM sales
GROUP BY region

`region`	`non_null_amounts`
`"North"`	`3`
`"South"`	`1`

The South region has 2 sales, but only 1 has a non-NULL amount.

Important Notes¶

COUNT(*) counts all rows, including those with NULL values in any column
COUNT(column) counts only rows where the specified column is not NULL
When used without GROUP BY, COUNT returns a single value for the entire table
When used with GROUP BY, COUNT returns one value per group
The return type is always BIGINT
Index Requirement: For optimal performance, queries with GROUP BY require an appropriate index. See Indexes for details on creating indexes that support GROUP BY operations.