Return highest count of one field when duplicate found on anotherfield

[elbyc]

I have a table with department IDs, department heads and headcount.
Sometimes there are two department heads per department ID. I want to
show the head with the largest staff as the leader. I've created a
total query to show the count of the staff. Where can I go from there?

SELECT [DeptHeadcountTable].DeptID, [DeptHeadcountTable].[Director],
Count([DeptHeadcountTable].[EmployeeID]) AS [CountOfEmployeeID]
FROM [DeptHeadcountTable]
GROUP BY [DeptHeadcountTable].DeptID, [DeptHeadcountTable].[Director];

 

[KARL DEWEY]

Try this --
SELECT [DeptHeadcountTable].DeptID, (SELECT TOP 1 [XX].[Department Head]
FROM [DeptHeadcountTable] AS [XX] WHERE [XX].DeptID =
[DeptHeadcountTable].DeptID ORDER BY Count([XX].[EmployeeID]) DESC) AS
Director
FROM [DeptHeadcountTable]
ORDER BY [DeptHeadcountTable].DeptID;

 

[elbyc]

Try this --
SELECT [DeptHeadcountTable].DeptID, (SELECT TOP 1 [XX].[Department Head]  
FROM [DeptHeadcountTable] AS [XX] WHERE [XX].DeptID =
[DeptHeadcountTable].DeptID ORDER BY Count([XX].[EmployeeID]) DESC) AS  
Director        
FROM [DeptHeadcountTable]
ORDER BY [DeptHeadcountTable].DeptID;

Hi, Thanks. I am not sure how to translate. What does the XX stand
for? I tried your SQL exactly but it did not recognize "[XX].
[Department Head]". When I changed that to [XX].[Director] it said I
tried to execute a query that does not include the specified
expression "Director" as part of an aggregate function.

What is the name of this function? Top?

 

[elbyc]

Try this --
SELECT [DeptHeadcountTable].DeptID, (SELECT TOP 1 [XX].[Department Head]  
FROM [DeptHeadcountTable] AS [XX] WHERE [XX].DeptID =
[DeptHeadcountTable].DeptID ORDER BY Count([XX].[EmployeeID]) DESC) AS  
Director        
FROM [DeptHeadcountTable]
ORDER BY [DeptHeadcountTable].DeptID;

Hi, Thanks. I am not sure how to translate. What does the XX stand
for? I tried your SQL exactly but it did not recognize "[XX].
[Department Head]". When I changed that to [XX].[Director] it said I
tried to execute a query that does not include the specified
expression "Director" as part of an aggregate function.

What is the name of this function? Top?

never mind - I see what you did. I looked up Subquery and found this
useful tutorial. Thanks for the lead:
http://allenbrowne.com/subquery-01.html

 

[elbyc]

The following query should return the MAX headcount per department:

SELECT DeptID, MAX(Headcount) As MaxHeadcount
FROM (SELECT DeptID, COUNT(*) AS Headcount
            FROM DeptHeadcountTable
            GROUP BY DeptID, Director)
GROUP BY DeptID;

Save this query then join it to your original query like so:

SELECT Query1.*
FROM Query1 INNER JOIN Query2
ON Query1.DeptID = Query2.DeptID
AND Query1.CountOfEmployeeID = Query2.MaxHeadcount;

where Query1 is the original query and Query2 the second query.  Any
department which has the same headcount for both directors will be returned
twice of course.

Ken Sheridan
Stafford, England

I have a table with department IDs, department heads and headcount.
Sometimes there are two department heads per department ID. I want to
show the head with the largest staff as the leader. I've created a
total query to show the count of the staff. Where can I go from there?

SELECT [DeptHeadcountTable].DeptID, [DeptHeadcountTable].[Director],
Count([DeptHeadcountTable].[EmployeeID]) AS [CountOfEmployeeID]
FROM [DeptHeadcountTable]
GROUP BY [DeptHeadcountTable].DeptID, [DeptHeadcountTable].[Director];

Elegant. Thanks.