COUNTDISTINCT

COUNTDISTINCT refers to the SQL construct used to count the number of distinct (non-NULL) values in a column or expression. The standard form is COUNT(DISTINCT expression), an aggregate function that returns the number of unique values produced by the expression within the query’s scope. It is supported by most relational database systems, though syntax can vary for counting distinct across multiple columns. Unlike COUNT(*), which returns the total number of rows, COUNT(DISTINCT ...) counts unique values only. NULL values are not counted by COUNT(DISTINCT ...).

Usage examples: In a table orders with a customer_id, you can count how many different customers placed orders with SELECT COUNT(DISTINCT customer_id) FROM orders;. For counting distinct combinations of two columns in PostgreSQL, you can write SELECT COUNT(DISTINCT (city, state)) FROM addresses;. In other systems, counting multiple columns may require concatenation or a derived expression, e.g., COUNT(DISTINCT CONCAT(city, '|', state)).

NULL handling and performance: NULL values are ignored in COUNT(DISTINCT). When many values are distinct, the operation may be expensive because the database must identify uniqueness, often via hashing or sorting. Performance can be improved with appropriate filtering, smaller result sets, or indexing. For very large datasets or streaming scenarios, some systems offer approximate distinct counts, such as APPROX_COUNT_DISTINCT, as alternatives to exact counts.

Dialect notes: Syntax and capabilities vary by database. Most systems implement COUNT(DISTINCT column) for a single column; counting distinct combinations of multiple columns may be supported (as a row constructor in some databases) or may require workarounds such as expression concatenation. Users should consult their system’s documentation for exact behavior.