PDQ.com mobilePDQ.com desktop
Support

SQL COUNT DISTINCT vs. DISTINCT

Stephanie WarnerStephanie Warner
·

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:

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 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:

SELECT DISTINCT Column1, Column2, ColumnN
FROM Table;

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.

Ready to get started with PDQ Deploy & Inventory?

Take our 14-day Free Trial.
This round is on us!

Don't miss the next post!

SigRed Critical Vulnerability and Patch/Fix

SigRed Critical Vulnerability and Patch/Fix

PDQ.com
© 2020 PDQ.com Corporation

Products

  • Deploy
  • Inventory
  • Enterprise SL
  • Pricing
  • Downloads
  • Licensing
  • Buy