The COUNT function counts the rows defined by the expression.
The COUNT function has three variations. COUNT ( * ) counts all the rows in the target table whether they include nulls or not. COUNT ( expression ) computes the number of rows with non-NULL values in a specific column or expression. COUNT ( DISTINCT expression ) computes the number of distinct non-NULL values in a column or expression.
[ APPROXIMATE ] COUNT ( [ DISTINCT | ALL ] * | expression )
The target column or expression that the function operates on.
- DISTINCT | ALL
With the argument DISTINCT, the function eliminates all duplicate values from the specified expression before doing the count. With the argument ALL, the function retains all duplicate values from the expression for counting. ALL is the default.
When used with APPROXIMATE, a COUNT ( DISTINCT expression ) function uses a HyperLogLog algorithm to approximate the number of distinct non-NULL values in a column or expression. Queries that use the APPROXIMATE keyword execute much faster, with a low relative error of around 2%. Approximation is warranted for queries that return a large number of distinct values, in the millions or more per query, or per group, if there is a group by clause. For smaller sets of distinct values, in the thousands, approximation might be slower than a precise count. APPROXIMATE can only be used with COUNT ( DISTINCT ).
The COUNT function supports all argument data types.
The COUNT function returns BIGINT.
Count all of the users from the state of Florida:
select count (*) from users where state='FL'; count ------- 510 (1 row)
Count all of the unique venue IDs from the EVENT table:
select count (distinct venueid) as venues from event; venues -------- 204 (1 row)
Count the number of times each seller listed batches of more than four tickets for sale. Group the results by seller ID:
select count(*), sellerid from listing group by sellerid having min(numtickets)>4 order by 1 desc, 2; count | sellerid -------+---------- 12 | 17304 11 | 25428 11 | 48950 11 | 49585 ... (16840 rows)
The following examples compare the return values and execution times for COUNT and APPROXIMATE COUNT.
select count(distinct pricepaid) from sales; count ------- 4528 (1 row) Time: 48.048 ms select approximate count(distinct pricepaid) from sales; count ------- 4541 (1 row) Time: 21.728 ms