Solved

use count within a join query?

Posted on 2006-07-24
6
225 Views
Last Modified: 2008-02-01
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.
0
Comment
Question by:ottenm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 25

Expert Comment

by:Mr_Peerapol
ID: 17169877

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)
0
 
LVL 25

Assisted Solution

by:Mr_Peerapol
Mr_Peerapol earned 250 total points
ID: 17169881
Sorry, the first one should be:

SELECT *, (SELECT COUNT(*) FROM CUSTOMERS WHERE SALES_REPS.repid = CUSTOMERS.repid) AS [customer counts] FROM SALES_REPS
0
 
LVL 19

Expert Comment

by:folderol
ID: 17170136
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
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

Author Comment

by:ottenm
ID: 17170283
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.
0
 
LVL 19

Accepted Solution

by:
folderol earned 250 total points
ID: 17170439
Just remove that line
case grouping(SALES_REPS.repid) when 0 then SALES_REPS.repid else 'Total Count of Reps' end,
and put

SALES_REPS.repid,

in its place.  OR, change it to

case grouping(SALES_REPS.repid) when 0 then SALES_REPS.LAST_NAME else 'Total Count of Reps' end,

or other varchar field in SALES_REPS table.



Below is a simplified query to just return the 2nd part of your post.
--------
select
replist.Cust_count,
count(SALES_REPS.repid) as 'Rep_count'
from
(
select
CUSTOMERS.repid, Count(CUSTOMERS.repid) as 'Cust_count'
from CUSTOMERS
group by CUSTOMERS.repid
) as replist
group by replist.Cust_count with rollup
order by replist.Cust_count desc


Tom.
0
 

Author Comment

by:ottenm
ID: 17170618
That's them!  Awesome!! Thank you very much!
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question