Distinct Count Of Records

[MDW]

Say I've got a job category table that looks like this
JOB_CODE CAT TITLE
A01 M Manager 1
A02 M Manager 2
S01 A Assistant 1
S02 A Assistant 2

and I've got an employee detail table like this
EMP_ID JOB_CODE START_DATE END_DATE
E001 S01 1/1/05 2/1/05
E001 S02 2/2/05 <NULL>
E002 A02 1/1/05 1/6/05
E002 A01 1/7/05 3/15/05
E002 S01 3/16/05 <NULL>

I'm trying to get a headcount of how many people fell into each job category
(not job code). For instance, employee E001 worked in two different jobs, but
they are both in the same category, so that person should only be counted
once. Employee E002 worked three jobs across two categories, so they should
be counted twice.

Thus, the result would be
CAT HEADCOUNT
M 1
A 2

I can't figure out how to make this query work. Thanks.

 

[Marshall Barton]

Say I've got a job category table that looks like this
JOB_CODE CAT TITLE
A01 M Manager 1
A02 M Manager 2
S01 A Assistant 1
S02 A Assistant 2

and I've got an employee detail table like this
EMP_ID JOB_CODE START_DATE END_DATE
E001 S01 1/1/05 2/1/05
E001 S02 2/2/05 <NULL>
E002 A02 1/1/05 1/6/05
E002 A01 1/7/05 3/15/05
E002 S01 3/16/05 <NULL>

I'm trying to get a headcount of how many people fell into each job category
(not job code). For instance, employee E001 worked in two different jobs, but
they are both in the same category, so that person should only be counted
once. Employee E002 worked three jobs across two categories, so they should
be counted twice.

Thus, the result would be
CAT HEADCOUNT
M 1
A 2

I think this is the kind of thing you want:

SELECT Cat, Count(*) As HeadCount
FROM (SELECT DISTINCT C.Cat, E.EMP_ID
FROM EmployeeDetail As E
INNER JOIN JobCategory As C
ON E.Job_Code = C.Job_Code)
GROUP BY C.Cat

 

[MDW]

Thanks, I'll gve that a try.