ottenm
asked on
use count within a join query?
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try:
select
case grouping(SALES_REPS.repid) when 0 then SALES_REPS.repid else 'Total Count of Reps' end,
replist.Cust_count,
count(SALES_REPS.repid) as 'Rep_count'
from
SALES_REPS join
(
select
CUSTOMERS.repid, Count(CUSTOMERS.repid) as 'Cust_count'
from CUSTOMERS
group by CUSTOMERS.repid
) as replist on SALES_REPS.repid = replist.repid
group by SALES_REPS.repid, replist.Cust_count with rollup
order by replist.Cust_count desc
Since each rep can have only one value for customer count, the double group by has no effect other than triggering the rollup on both columns.
Rollup sometimes returns more rows than you want or need but there are techniques for removing them from the results.
Tom
select
case grouping(SALES_REPS.repid)
replist.Cust_count,
count(SALES_REPS.repid) as 'Rep_count'
from
SALES_REPS join
(
select
CUSTOMERS.repid, Count(CUSTOMERS.repid) as 'Cust_count'
from CUSTOMERS
group by CUSTOMERS.repid
) as replist on SALES_REPS.repid = replist.repid
group by SALES_REPS.repid, replist.Cust_count with rollup
order by replist.Cust_count desc
Since each rep can have only one value for customer count, the double group by has no effect other than triggering the rollup on both columns.
Rollup sometimes returns more rows than you want or need but there are techniques for removing them from the results.
Tom
ASKER
This one is perfect for counting the number of customers each rep has:
SELECT *, (SELECT COUNT(*) FROM CUSTOMERS WHERE SALES_REPS.repid = CUSTOMERS.repid) AS [customer counts] FROM SALES_REPS
To find out how many reps have 10 customers, and how many have 11, etc., I get the following error when running the solution by folderol:
Syntax error converting the varchar value 'Total Count of Reps' to a column of data type int.
SELECT *, (SELECT COUNT(*) FROM CUSTOMERS WHERE SALES_REPS.repid = CUSTOMERS.repid) AS [customer counts] FROM SALES_REPS
To find out how many reps have 10 customers, and how many have 11, etc., I get the following error when running the solution by folderol:
Syntax error converting the varchar value 'Total Count of Reps' to a column of data type int.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That's them! Awesome!! Thank you very much!
SELECT s.*, (SELECT COUNT(*) FROM CUSTOMERS WHERE SALES_REPS.repid = CUSTOMERS.repid) AS [customer counts] FROM SALES_REPS
SELECT (SELECT COUNT(*) FROM SALES_REPS), (SELECT COUNT(*) FROM CUSTOMERS)