SQL COUNT DISTINCT vs. DISTINCT

COUNT DISTINCT

COUNT() with the DISTINCT clause removes duplicate rows of the same data in the result set. It also removes ‘NULL’ values in the result set. The correct syntax for using COUNT(DISTINCT) is:

The distinct count will be based off the column in parenthesis. The result set should only be one row, an integer/number of the column you’re counting distinct values of.

Example

You want to know how many customers have a ‘Purchased’ status from your Customers table. You only want each customer counted once, regardless of how many times they have purchased. An example of what this query would look like is:

The column whose values are being counted is the CustomerId column. The result set should only render 1 row, the number of purchased customers.

Moving on…

You now know the number of purchased customers but want a list of each distinct customer with their CustomerId and Name. The result set will be based off the column the DISTINCT clause is paired with in the SELECT statement. The query will return the DISTINCT number of rows that appease the conditions listed in the WHERE clause, if any.

DISTINCT

The DISTINCT clause will also remove duplicate values in the result set. The syntax for using DISTINCT without the COUNT clause is:

Note: In these examples, we’re using the same DISTINCT column and conditions meaning the number of rows in the result set should match the distinct count from the previous example. 

Example

You want to see a list of all customers who have a status of ‘purchased’ from your Customers table. You only need each customer listed once, regardless of how many purchases they have made. An example of what this query would look like is:

In this query, the DISTINCT clause is paired with the CustomerId column meaning that a CustomerId will only be listed one time in the result set. The condition in the WHERE clause must be met therefore any CustomerId whose status is anything other than ‘Purchased’ would not be included in the result set.