Databases
StackOverflow2013
Postsdbo
POSQL Nested Aggregate Query- How do I get the number of customers per employee?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Nested Aggregate Query- How do I get the number of customers per employee?
    primarykey
    data
    text
    <p>The following is the prompt I need to answer:</p> <p>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.</p> <p>The following is my EMPLOYEES table:</p> <pre><code>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); </code></pre> <p>The following is my CUSTOMERS table:</p> <pre><code>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); </code></pre> <p>The following is my query:</p> <pre><code>select ename, empId from EMPLOYEES where EmpID in (select count(repid) as NumberOfCustomers from CUSTOMERS group by RepID); </code></pre> <p>Why is my query not working?</p> <p>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</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJeff Orris
    1. USJeff Orris
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL Nested Aggregate Query- How do I get the number of customers per employee?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSQL Nested Aggregate Query- How do I get the number of customers per employee?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POSQL Nested Aggregate Query- How do I get the number of customers per employee?
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO+1 for posting the scripts. All do like Jeff Oris does!
      singulars
      1. POSQL Nested Aggregate Query- How do I get the number of customers per employee?
      1. USQuassnoi
    2. CO@Quassnoi: Except for posting apparent homework :/
      singulars
      1. POSQL Nested Aggregate Query- How do I get the number of customers per employee?
      1. USmellamokb
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload