Skip to main content

Categories: Aggregate, Window

COUNT

Returns the total number of records for the specified expression.

Syntax​

COUNT(expression ANY) → BIGINT​

  • expression: The expression to evaluate. Can be an asterisk (*) or the column name of any primitive data type. Use an asterisk to include rows that contain NULL. Use a column name to ignore rows that contain NULL.

Examples

COUNT example 
SELECT COUNT(passenger_count) FROM "Samples"."samples.dremio.com"."NYC-taxi-trips";
-- 338293677
COUNT example: Window function with cumulative window frame 
SELECT city, state, pop, COUNT(pop)
  OVER (PARTITION BY state ORDER BY city RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM Samples."samples.dremio.com"."zips.json";
-- city, state, pop, EXPR$3
-- 98791, AK, 5345, 196
-- AKHIOK, AK, 13309, 195
-- AKIACHAK, AK, 481, 194
-- ...
COUNT example: Window function with sliding window frame 
SELECT city, state, pop, COUNT(pop)
  OVER (PARTITION BY state ORDER BY city ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING)
FROM Samples."samples.dremio.com"."zips.json";
-- city, state, pop, EXPR$3
-- 98791, AK, 5345, 3
-- AKHIOK, AK, 13309, 4
-- AKIACHAK, AK, 481, 4
-- ...

Usage Notes​

The COUNT function supports optional PARTITION BY, ORDER_BY, and cumulative and sliding window frame subclauses. See [Window Functions](See Window Functions) for more information and syntax.