Count of distinct (1 Viewer)

[wgma]

I should be able to do this but I my brain went home.

I have a table that has the following columns:

WorkerID
JobCode
WorkDate

I need to get the number of workers for each job code within a given date range. I need the display format to look something like this:

JobCode NumberOfWorkers
100 5
200 20

I just can't think how to do it.

Any help would be greatly appreciated.
Thanks.

 

[LPurvis]

Hi

I infer from your reference to Distinct that the same worker can appear more than once within the date range - but they're to be counted only once.

It's just of the theme:

SELECT JobCode, COUNT(*) As NumberOfWorkers
FROM 
  (SELECT DISTINCT JobCode, WorkerID 
   FROM TableName 
   WHERE WorkDate BETWEEN [DateStart] AND [DateEnd])
GROUP BY JobCode

Of course you can GROUP BY rather than DISTINCT if you choose/prefer.
(In pervious tests DISTINCT can outperform Grouping in Jet).

 

[wgma]

That worked. Thanks.

 

[LPurvis]

No problemo.