The COUNT() function with the DISTINCT clause is used to count the number of unique values in a column.
Example
SELECT COUNT(DISTINCT country)
FROM Customers;Here, the SQL command counts and returns the number of unique country values in the Customers table.
COUNT() With DISTINCT and WHERE
We can also combine COUNT() with DISTINCT and WHERE clauses in a single query. For example,
SELECT COUNT(DISTINCT country) AS countries_with_customers_over_25
FROM Customers
WHERE age > 25;Here, the SQL command counts the number of unique countries for customers older than 25.
COUNT() With DISTINCT and GROUP BY
IN SQL, we can also utilize COUNT() with DISTINCT, and integrate it with GROUP BY.
Let's take a look at an example.
SELECT COUNT(DISTINCT customer_id) AS unique_customers_per_item
FROM Orders
GROUP BY item;Here, the SQL command calculates the number of unique customers who have ordered a specific item in the Orders table.