Steve Short Steve Short - 5 months ago 6
SQL Question

How to Query Data for Certain Result

okay here is my data set

Event_ID | WorkerNumberID
3 | Worker1
3 | Worker2
3 | Worker2
3 | Worker3
5 | Worker4
5 | Worker5
3 | Null
5 | Null


I want my query to return the below. Even though eventID 3 has 2 'worker2's I still only want it to count that as 1 non null item because they are the same.

Event_ID | WorkerNumberIDCount (a count of not null items)
3 | 3
5 | 2


Thoughts?

Answer

Try this

SELECT Event_ID,COUNT(DISTINCT WorkerNumberID) AS WorkerNumberIDCount 
FROM TableName
WHERE  WorkerNumberID IS NOT NULL
Group By Event_ID