Geek asks and answers

SQL Nested Aggregate Query. How to get the number of customers per employee?

Below is a hint that I need to answer:

Indicate the number of customers for each employee. Indicate the name and number of the employee. Customers marked accordingly, first list the employee with the largest number of customers.

The following is the 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);

Below is a table of my CLIENTS:

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);

Below is my request:

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

Why is my request not working?

I know that I want to map the empid from EMPLOYEES to the repID in CUSTOMERS, and then COUNT, how many times the rep identifier appears in CUSTOMERS. The only reason I need the EMPLOYEES table is to put Ename. Im confused in syntaxt which I need to use because I need to output the RepID counter in the CUSTOMERS table

+5
4

Common Table Expression, SQL Server 2005 .

;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

+1
SELECT  *
FROM    employees e
CROSS APPLY
        (
        SELECT  COUNT(*)
        FROM    customers c
        WHERE   c.repId = e.empId
        ) cc (cnt)
ORDER BY
        cnt DESC

. SQLFiddle

:

SELECT  *,
        (
        SELECT  COUNT(*)
        FROM    customers c
        WHERE   c.repId = e.empId
        ) cnt
FROM    employees e
ORDER BY
        cnt DESC
+1

Your internal request has count(repid)one that is the counter, not the actual EmployeeID that your external request requires at where EmpID in (xxx). Not sure why you want the count in the inner query because you will lose it in the final result, but this should work:

 select ename, empId
 from EMPLOYEES
 where EmpID in (
    select repID
    from CUSTOMERS);
0
source

How about this one? or I misunderstood

 SELECT ename, count(*) as NumCustomers 
FROM Customers INNER JOIN 
 Employees ON customers.repid = employees.empid GROUP BY repid,ename 
 ORDER BY numCustomers DESC
0
source

More articles:


All Articles