Skip to main content

COUNT

Returns the total number of records in a resource or a count of the non NULL instances of a column in a grouping.

See also:
[SELECT]

Syntax​

SELECT COUNT( * | columnExpression ) FROM <multipartIdentifier>
[ GROUP BY groupByColumn ];

Arguments​

columnExpression or *
A column or expression. If * is specified then all records in the resource or grouping are counted.

At least one value from the columnExpression must be non NULL otherwise NULL is returned.

groupByColumn
A column or columns used to perform summary or aggregate operations against. The GROUP BY clause returns one row for each column grouping.

Return Value(s)​

Returns an integer representing the count.

Examples​

Return the average of a column expression over an entire resource​

SELECT count(*) as num_instances
FROM google.compute.instances 
WHERE project = 'stackql-demo' 
AND zone = 'australia-southeast1-a';

Return the average of a column expression grouped by another column expression​

SELECT tags, count(*) as num_instances
FROM google.compute.instances 
WHERE project = 'stackql-demo' 
AND zone = 'australia-southeast1-a'
GROUP BY json_extract(tags, '$.instanceType');

Use COUNT as a window function to calculate cumulative counts​

-- Track cumulative issue counts over time
SELECT
    number,
    title,
    state,
    created_at,
    COUNT(*) OVER (ORDER BY created_at) as cumulative_issues,
    COUNT(*) OVER (PARTITION BY state ORDER BY created_at) as cumulative_by_state
FROM github.issues.issues
WHERE owner = 'stackql'
  AND repo = 'stackql'
ORDER BY created_at;

For more information, see https://www.sqlite.org/lang_aggfunc.html#count.