Search code examples
sqlsql-servercountsubsetdistinct

Distinct Count & Distinct Count with Condition in a Single Query

Model ID Work Order Number Purchase Order Lines
123 x 5450
123 x 5400
123 y 5200
123 y 5500

I have something like the table above in my SQL Server database. I want a query to return the the distinct Model ID, count of distinct Work Orders, and distinct count of work orders where Purchase Order Lines are not 5450 or 5400.

From the table above the result of the query should be as follows:

Model ID Distinct Work Orders Distinct Work Orders excluding PO lines 5400 and 5450
123 2 1

Is there a way to do this without using multiple sub queries or temporary tables?

Solution

  • You can use COUNT DISTINCT with a case statement. Something like this...

    SELECT COUNT(DISTINCT ColumnName)
      , COUNT(DISTINCT ( CASE WHEN SomeCondition = True THEN ColumnName ELSE NULL END)) FilteredDistCount 
FROM TableName