use count within a join query?
Posted on 2006-07-24
I would like to write two related queries.
The first tells me, for each row in SALES_REPS, how many customers each rep is responsible for (how many rows in CUSTOMERS have SALES_REPS.repid = CUSTOMERS.repid). If I call these values 'customer counts', then I also want a second query that tallies the customer counts (5 employees have 25 customers, 8 employees have 32 customers, 18 employees have 28 customers, etc.).
I can calculate the values in code walking through the result set, but I would prefer to bring the results directly out of a SQL statement.
Thanks for any help.