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
SELECT COUNT(DISTINCT Column1)
FROM Table;
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 just started a business and want to know how many sales territories your representatives have sold to so far. Some countries have more than one sales territory, but you’d like the total of all of them in your result set. You only want each distinct territory counted once. An example of what this query would look like is:
Note: The column was given the alias ‘Territory Count’ using AS. Otherwise, the result set would say, ‘No Column Name’
The column whose values are being counted is the ‘TerritoryId’ column. The result set should only render 1 row, the distinct number of sales territories.
Now that you know your representatives have sold to 10 total sales territories, you want to know how many of those are located within the United States. This will require a condition in the where clause and the query example would look something like:
Next, check out DISTINCT COUNT vs. DISTINCT in a SELECT statement or What are SQL set operators and how to use them.
