SQL COUNT DISTINCT

Stephanie Warner headshot
Stephanie Warner|June 15, 2020
image (76)
image (76)

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:

image3 (4)

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.

image1 (2)
Moving On...

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:

image4 (3)
image2 (2)

To see the difference because using DISTINCT COUNT vs. DISTINCT in a SELECT statement, click here.


Stephanie Warner headshot
Stephanie Warner

Stephanie is a big time math nerd, avid snowboarder, and overall winter lover. She enjoys the torture of running and traveling to work Ragnar Relay races. From shredding the gnar to getting far too invested in murder/crime documentaries, you'll also find her petting all the doggos. She was an employee of PDQ.

Related Articles