CODEWITHSUNDEEP
sqlsql-servercountsubsetdistinct
Omar123456789
Omar123456789

Reputation: 61

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?

Upvotes: 0

Views: 318

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I suspect that you might actually mean work orders that do not have 5400 or 5450, even if they have other values.

You can do this with a difference:

select count(distinct workorder),
       (count(distinct workorder) - 
        count(distinct case when purchaseorder not in (5400, 5450) then workorder end)
       )
from t;

Upvotes: 1

M.Ali
M.Ali

Reputation: 69524

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

Upvotes: 0

Related Questions