SQL Query Question (Possible Join / Subquery?)

Question

Hi,

I have two tables (Management and Employee).

The management table tracks the different management teams that have managed company X in past few years. Each management team is given an ID (i.e: managementnr), and each team has a CEO (namely ceoname).

The employee table tracks employees working for company X (basically just their names and which management team hired them)

Here is the SQL code:

CREATE TABLE EMPLOYEE(
    EMPLOYEENAME   VARCHAR2(15) NOT NULL,
    HIRETEAM NUMBER(2), 
    PRIMARY KEY (EMPLOYEENAME)
    );

CREATE TABLE MANAGEMENT(
    MANAGEMENTNR NUMBER(2) NOT NULL,
    CEONAME VARCHAR2(20) NOT NULL,
    PRIMARY KEY (MANAGEMENTNR,CEONAME)
    ); 

I'm trying to form an SQL query to get the managementNR in which the highest number of employees have been hired. I've tried using:

SELECT HIRETEAM,max(count(HIRETEAM)) 
 from EMPLOYEE 
group by HIRETEAM

but I keep getting:

  ORA-00937: not a single-group group function

(I'm using Oracle)

After hours of Google-fu, I finally understand what this error means, however I still cannot think of any other way to form this query. I've tried using subqueries / joins, but still nothing. I'm very new to SQL in general. If anybody can help, I would be really grateful!

Thank you for your time =]
--Evan Lestrange

Answer 1

Get the first row from this query:

select hireteam,count(*) from EMPLOYEE group by hireteam order by count(*) desc

Answer 2

This way you get all teams with the number of hiered employees.

select
   Management.ManagementNr,
   Management.CeoName,
   count(Employee.EmployeeName)   
from Employee inner join Management   
on Employee.HireTeam = Management.ManagementNr   
group by Management.ManagementNr   
order by count(Employee.EmployeeName) desc

Adding a additional having clause and abdonning the join (because the CEO name is not required as I just recognized) you get your desired result as follows.

select Employee.HireTeam   
from Employee   
group by Employee.HireTeam   
having count(EmployeeName) = select max(GroupSize)
   from select count(EmployeeName) as GroupSize
      from Employee group by Employee.HireTeam

This may actually return multiple rows if several managments hired the same number of employees. And it is ugly ... but ad hoc I know no better way to do it in a single querry.

Answer 3

I think you are looking for this:

WITH counts_hireteam as (
   SELECT HIRETEAM
        , count(*) count_hireteam
     from EMPLOYEE 
   group by HIRETEAM
   order by count(*) desc
) 
select HIRETEAM  
  from counts_hireteam
 where rownum = 1;

Answer 4

The "correct" way to do this is actually something like:

SELECT m.ceoname, COUNT(1), ROW_NUMBER() OVER (ORDER BY COUNT(1) DESC)
FROM management m
JOIN employee e ON m.managementnr = e.hireteam
GROUP BY m.ceoname

You're getting into the murkky area of Top-N queries and I'll direct you to this excellent column of Ask Tom on Top-N. Other answers work in simple cases but it's when you start wanting to do things like returning the top 3 management teams (by head count) and doing so consistently that you'll run into problems.

Consistency is a key point. As Tom notes:

Does a GROUP BY clause in a query guarantee that the output data will be sorted on the GROUP BY columns in order, even if there is no ORDER BY clause?

Unless and until there is an ORDER BY statement on a query, the rows returned cannot be assumed to be in any order. Without an ORDER BY, the data may be returned in any order in which the database feels like returning it. This has always been true and will always be true.

In fact, in Oracle Database 10g Release 2, you'll see GROUP BY returning data in a random order much more often than before:

Using this sample data:

INSERT INTO employee VALUES ('Bob',1);
INSERT INTO employee VALUES ('Sue',1);
INSERT INTO employee VALUES ('John',1);
INSERT INTO employee VALUES ('James',2);
INSERT INTO employee VALUES ('Mary',2);
INSERT INTO employee VALUES ('Ron', 2);
INSERT INTO employee VALUES ('Jane',3);
INSERT INTO employee VALUES ('Luke',4);
INSERT INTO employee VALUES ('Rob',4);
INSERT INTO employee VALUES ('Tim', 5);

INSERT INTO management VALUES (1, 'Kate');
INSERT INTO management VALUES (2, 'Larry');
INSERT INTO management VALUES (3, 'Jake');
INSERT INTO management VALUES (4, 'Sarah');
INSERT INTO management VALUES (5, 'Tom');

Answer 5

If you want to limit the results to a single record, even in the case of ties for the greatest number of hires:

select * from (
    select hireteam, count(hireteam) count
    from employee
    group by hireteam
    order by count(hireteam) desc, hireteam asc )
where rownum = 1

To retrieve all hireteam values having the most hires (including ties):

select * from (
    select hireteam,
           count(hireteam) count,
           rank() over (order by count(hireteam) desc) rank
    from   employee
    group by hireteam)
where rank = 1;