SQL Nested Aggregate Query- How do I get the number of customers per employee?

Question

The following is the prompt I need to answer:

List the number of customers for each employee. Include employee's name and number of customers labeled appropriately, listing the employee with the most customers first.

The following is my EMPLOYEES table:

create table EMPLOYEES
    (EmpID    char(4)         unique Not null,
     Ename    varchar(10),
     Job      varchar(9),
     MGR      char(4),
     Hiredate date,
     Salary   decimal(7,2),
     Comm     decimal(7,2),
     DeptNo   char(2)         not null,
         Primary key(EmpID),
         Foreign key(DeptNo) REFERENCES DEPARTMENTS(DeptNo));


insert into EMPLOYEES values (7839,'King','President',null,'17-Nov-11',5000,null,10);
insert into EMPLOYEES values (7698,'Blake','Manager',7839,'01-May-11',2850,null,30);
insert into EMPLOYEES values (7782,'Clark','Manager',7839,'02-Jun-11',2450,null,10);
insert into EMPLOYEES values (7566,'Jones','Manager',7839,'02-Apr-11',2975,null,20);
insert into EMPLOYEES values (7654,'Martin','Salesman',7698,'28-Feb-12',1250,1400,30);
insert into EMPLOYEES values (7499,'Allen','Salesman',7698,'20-Feb-11',1600,300,30);
insert into EMPLOYEES values (7844,'Turner','Salesman',7698,'08-Sep-11',1500,0,30);
insert into EMPLOYEES values (7900,'James','Clerk',7698,'22-Feb-12',950,null,30);
insert into EMPLOYEES values (7521,'Ward','Salesman',7698,'22-Feb-12',1250,500,30);
insert into EMPLOYEES values (7902,'Ford','Analyst',7566,'03-Dec-11',3000,null,20);
insert into EMPLOYEES values (7369,'Smith','Clerk',7902,'17-Dec-10',800,null,20);
insert into EMPLOYEES values (7788,'Scott','Analyst',7566,'09-Dec-12',3000,null,20);
insert into EMPLOYEES values (7876,'Adams','Clerk',7788,'12-Jan-10',1100,null,20);
insert into EMPLOYEES values (7934,'Miller','Clerk',7782,'23-Jan-12',1300,null,10);

The following is my CUSTOMERS table:

create table CUSTOMERS
    (CustID      char(6)       unique Not null,
     Name        varchar(45),
     Address     varchar(40),
     City        varchar(30),
     State       varchar(2),
     Zip         varchar(9),
     AreaCode    char(3),
     Phone       varchar (9),
     RepID       char(4)       not null,
     CreditLimit decimal(9,2),
     Primary key(CustID),
         Foreign key(RepID) References EMPLOYEES(EmpID));


insert into CUSTOMERS values (100,'Jocksports','345 Viewridge','Belmont','CA','96711',415,'598-6609',7844,5000);
insert into CUSTOMERS values (101,'TKB Sport Shop','490 Boli Rd.','Redwood City','CA','94061',415,'368-1223',7521,10000);
insert into CUSTOMERS values (102,'Vollyrite','9722 Hamilton','Burlingame','CA','95133',415,'644-3341',7654,7000);
insert into CUSTOMERS values (103,'Just Tennis','Hillview Mall','Burlingame','CA','97544',415,'677-9312',7521,3000);
insert into CUSTOMERS values (104,'Every Mountain','574 Surry Rd.','Cupertino','CA','93301',408,'996-2323',7499,10000);
insert into CUSTOMERS values (105,'K + T Sports','3476 El Paseo','Santa Clara','CA','91003',408,'376-9966',7844,5000);
insert into CUSTOMERS values (106,'Shape Up','908 Sequoia','Palo Alto','CA','94301',415,'364-9777',7521,6000);
insert into CUSTOMERS values (107,'Womens Sports','Valco Village','Sunnyvale','CA','93301',408,'967-4398',7499,10000);
insert into CUSTOMERS values (108,'North Woods Fitness Supply Center','98 Lone Pine Way','Hibbing','MN','55649',612,'566-9123',7844,8000);

The following is my query:

select ename, empId
from EMPLOYEES
where EmpID in
(select count(repid) as NumberOfCustomers
from CUSTOMERS
group by RepID);

Why is my query not working?

I know I want to match the empid from EMPLOYEES to the repID in CUSTOMERS and then COUNT how many times the rep ID shows up in CUSTOMERS. The only reason I need the EMPLOYEES table is to out put Ename. Im confused about the syntaxt I need to use because I need to output the count of RepID in the CUSTOMERS table

Answer 1

You can also use Common Table Expression if you are using SQL Server 2005 and above.

;WITH CTE
AS
(
   SELECT RepID, COUNT(*) AS CNT
   FROM CUSTOMERS 
   GROUP BY REPID  
)

SELECT E.Ename, E.EmpID, ISNULL(C.CNT, 0) 
FROM EMPLOYEES E
LEFT OUTER JOIN CTE C ON C.RepID = E.EmpID

DEMO