Using Nested Counts in SQL

[Dan Czarnecki]

Hello,
I'm having a bit of trouble as to what I have to do for a certain problem for the current assignment I'm doing for my database class.

Here's the schema of the relations I would need to access from the SQL database:
employees(employeeNumber, lastName, firstName, extension, email, officeCode, reportsTo, jobTitle)
customers (customerNumber, customerName, contactLastName, contactFirstName, phone, addressLine1, addressLine2, city, state, postalCode, country, salesRepEmployeeNumber, creditLimit)

Here's the question that I'm getting stumped on:
4.For each Sales Rep, give me a table with their name (First and Last) and the number of clients they service.

I know that I would have to select the firstName, lastName, and jobFunction attributes from the employees relation to get the ones that are sales reps, but then I am stumped from there as to what I would have to do to get the count of the clients they service.

I'm guessing that I would have to do a nested count operation in the where portion of the 1st statement, but I don't know where to go from there.

[Les Morgan]

You need a group by for the employee number to make it unique line for them and do an outer join on the client table on the employee number, then sum anything from that client that will not be null if the client exists.

[Roel De Nijs]

Dan Czarnecki wrote:Here's the question that I'm getting stumped on:
4.For each Sales Rep, give me a table with their name (First and Last) and the number of clients they service.

I know that I would have to select the firstName, lastName, and jobFunction attributes from the employees relation to get the ones that are sales reps, but then I am stumped from there as to what I would have to do to get the count of the clients they service.

I'm guessing that I would have to do a nested count operation in the where portion of the 1st statement, but I don't know where to go from there.

Try to split it into smaller parts and try solving these first. Once that's done, you need to figure out a way to combine both results.

So I guess you can divide this problem into two smaller parts (queries):
1/ list first and last name for each sales rep
2/ list the sales rep employee number with the number of clients he/she serves

[Dan Czarnecki]

I'm guessing that I would have to do a nested count operation in the where portion of the 1st statement, but I don't know where to go from there.
Try to split it into smaller parts and try solving these first. Once that's done, you need to figure out a way to combine both results. So I guess you can divide this problem into two smaller parts (queries):
1/ list first and last name for each sales rep
2/ list the sales rep employee number with the number of clients he/she serves

Ok. Well, I understand how to do the first part, listing the first and last name for each sales rep (just done using Select firstName, lastName, jobTitle from employees, customers Where jobTitle = "Sales Rep"), but am still unsure of how I would get the count operation done.

Would I do a separate count statement within another select statement within the Where clause with the customerName or customerNumber? Or is there another way I could do this?

I've tried using the count function in the initial select statement, but that would only give me back one customer and a count.

I then tried using a group by after the statement and it give me back multiple instances of that same customer that I got in my previous attempt.

[Roel De Nijs]

Dan Czarnecki wrote:Ok. Well, I understand how to do the first part, listing the first and last name for each sales rep (just done using Select firstName, lastName, jobTitle from employees, customers Where jobTitle = "Sales Rep"), but am still unsure of how I would get the count operation done.

Indeed!

And what would be the query for only the 2nd point? So without listing first and last name, just salesRepEmployeeNumber with the number of clients.

Once you have both queries you can combine them. Probably the most easy approach is to use one of the queries in the FROM clause (e.g. in MySSQL). There's also another alternative joining both tables and using an aggregate function like MIN or MAX.

[Dan Czarnecki]

Indeed!

And what would be the query for only the 2nd point? So without listing first and last name, just salesRepEmployeeNumber with the number of clients.

Once you have both queries you can combine them. Probably the most easy approach is to use one of the queries in the FROM clause (e.g. in MySSQL). There's also another alternative joining both tables and using an aggregate function like MIN or MAX

Ok, so would I do something like Select employeeNumber, jobTitle, count(customerNumber) from employees Where jobTitle = "Sales Rep"?

And then to join them, I would use the query above within the from clause and then group them by employeeNumber?

[Roel De Nijs]

Dan Czarnecki wrote:Ok, so would I do something like Select employeeNumber, jobTitle, count(customerNumber) from employees Where jobTitle = "Sales Rep"?

That would not work for the 2nd part, because table employees doesn't have a column customerNumber.

[Dan Czarnecki]

That would not work for the 2nd part, because table employees doesn't have a column customerNumber.

Oh, ok.

What if I did it like this: Select salesRepEmployeeNumber, count(customerNumber) from customers

I know that would only give me one thing back, but am I on the right track for what I would need to put into that from clause?

[Roel De Nijs]

Dan Czarnecki wrote:What if I did it like this: Select salesRepEmployeeNumber, count(customerNumber) from customers

That gives you the required info for part two!

Dan Czarnecki wrote:I know that would only give me one thing back, but am I on the right track for what I would need to put into that from clause?

Now you can combine both queries
1/ Select firstName, lastName from employees Where jobTitle = "Sales Rep"
2/ Select salesRepEmployeeNumber, count(customerNumber) from customers
like this:Select e.firstName, e.lastName, c.cnt from employees e, ( Select salesRepEmployeeNumber, count(customerNumber) as cnt from customers ) c Where e.employeeNumber = c.salesRepEmployeeNumber and e.jobTitle = "Sales Rep"And it should give you what you are looking for...

[Dan Czarnecki]

That gives you the required info for part two!

Now you can combine both queries
1/ Select firstName, lastName from employees Where jobTitle = "Sales Rep"
2/ Select salesRepEmployeeNumber, count(customerNumber) from customers
like this:Select e.firstName, e.lastName, c.cnt from employees e, ( Select salesRepEmployeeNumber, count(customerNumber) as cnt from customers ) c Where e.employeeNumber = c.salesRepEmployeeNumber and e.jobTitle = "Sales Rep"And it should give you what you are looking for...

Ok, I've put the query in, and it seems to work, but it gave me back only 1 sales rep still. Looking at the data that has been given to me, I know that there is more than one sales rep.

I somewhat understand what's going on here now, but I still don't get why I'm only still getting one result back.

[Roel De Nijs]

Dan Czarnecki wrote:Ok, I've put the query in, and it seems to work, but it gave me back only 1 sales rep still. Looking at the data that has been given to me, I know that there is more than one sales rep.

I somewhat understand what's going on here now, but I still don't get why I'm only still getting one result back.

Understanding what's going on is the first step. If you do, it's easier to look for the issue

So what happens if you execute the 1st query Select firstName, lastName from employees Where jobTitle = "Sales Rep"? Do you get all expected records?

If it does, you should do the same with the 2nd query Select salesRepEmployeeNumber, count(customerNumber) from customers.

[Dan Czarnecki]

Understanding what's going on is the first step. If you do, it's easier to look for the issue

So what happens if you execute the 1st query Select firstName, lastName from employees Where jobTitle = "Sales Rep"? Do you get all expected records?

If it does, you should do the same with the 2nd query Select salesRepEmployeeNumber, count(customerNumber) from customers.

Ok, so I do get all the employees with the job title of sales rep when executing that first query separately, but as I mentioned before, I only get one of those employees back when doing that separate count.

[Roel De Nijs]

Dan Czarnecki wrote:but as I mentioned before, I only get one of those employees back when doing that separate count.

Oh, my mistake! I wrongly suggested (a few posts earlier) your query for the 2nd part was correct. And it's actually incorrect I would have expected an error message from your database executing that query, but clearly it didn't. Which database are you using?

Because you want to count all customer numbers for every sales rep, you'll need a group by statement. So the appropriate query should be Select salesRepEmployeeNumber, count(customerNumber) from customers group by salesRepEmployeeNumberSo that should give you for every sales rep number, the customer count. And if it does, you should update the all-in-one query accordingly. And my apologies for getting on the wrong track...

[Dan Czarnecki]

Oh, my mistake! I wrongly suggested (a few posts earlier) your query for the 2nd part was correct. And it's actually incorrect I would have expected an error message from your database executing that query, but clearly it didn't. Which database are you using?

Because you want to count all customer numbers for every sales rep, you'll need a group by statement. So the appropriate query should be Select salesRepEmployeeNumber, count(customerNumber) from customers group by salesRepEmployeeNumberSo that should give you for every sales rep number, the customer count. And if it does, you should update the all-in-one query accordingly. And my apologies for getting on the wrong track...

No worries! ;)

Now it works as it should!

Thanks again for everything!

[Dan Czarnecki]

Oh, my mistake! I wrongly suggested (a few posts earlier) your query for the 2nd part was correct. And it's actually incorrect I would have expected an error message from your database executing that query, but clearly it didn't. Which database are you using?

Because you want to count all customer numbers for every sales rep, you'll need a group by statement. So the appropriate query should be Select salesRepEmployeeNumber, count(customerNumber) from customers group by salesRepEmployeeNumberSo that should give you for every sales rep number, the customer count. And if it does, you should update the all-in-one query accordingly. And my apologies for getting on the wrong track...

And by the way, in answering your other question from before, I'm using MySQL version 5.6 with HeidiSQL.

[Dan Czarnecki]

Thanks again for all the help!

Now I know what to do for other problems like this. I should be able to use this same technique for all problems that consist of aggregate functions, right?

[Roel De Nijs]

Dan Czarnecki wrote:Thanks again for all the help!

Glad to hear it's working now.

Dan Czarnecki wrote:Now I know what to do for other problems like this. I should be able to use this same technique for all problems that consist of aggregate functions, right?

Similar problems could indeed be solved with this same technique.

Now what about trying the other alternative to get this working. I think it's a good exercise to try that as well because it will give expand your SQL experience and capabilities. And it's not that you are on your own, I'll assist you if it's needed

So first step would to join both tables using an inner join statement. And you need to select firstName, lastName, and customerNumber for only sales reps.

[Dave Tolls]

What if there's a sales rep with no customers?
Shouldn't they appear in the results?
Because (and this is just eye balling the suggestions, so I could well be wrong) this looks to me like they would be missed.

[Brian Tkatch]

Dave Tolls wrote:What if there's a sales rep with no customers?
Shouldn't they appear in the results?
Because (and this is just eye balling the suggestions, so I could well be wrong) this looks to me like they would be missed.

A GROUP BY will show all unique entries, zero count or not.

I have a different issue with the answers provided. The requirement was (emphasis mine), "For each Sales Rep, give me a table with their name (First and Last) and the number of clients they service."

If a separate table is required, that is, a result set, this would require two queries and a loop.

[Roel De Nijs]

Brian Tkatch wrote:A GROUP BY will show all unique entries, zero count or not.

I think Dave is refering to the fact if a sales rep (from employees) doesn't have any customers, no records for this sales rep will be in the customers table. And thus the salesRepEmployeeNumber for that sales rep will not be in the subquery and therefore not in the result set of the final query. And I agree with that comment.

Brian Tkatch wrote:I have a different issue with the answers provided. The requirement was (emphasis mine), "For each Sales Rep, give me a table with their name (First and Last) and the number of clients they service."

Yeah, I ignored that part of the question because that seems so weird. Why would you need a seperate table for each sales rep

[Brian Tkatch]

Roel De Nijs wrote:

Brian Tkatch wrote:A GROUP BY will show all unique entries, zero count or not.

I think Dave is refering to the fact if a sales rep (from employees) doesn't have any customers, no records for this sales rep will be in the customers table. And thus the salesRepEmployeeNumber for that sales rep will not be in the subquery and therefore not in the result set of the final query. And I agree with that comment.

Oh, ok, i just skimmed the answers provided. Sub-query or GROUP BY ought to do the job.

Roel De Nijs wrote:

Brian Tkatch wrote:I have a different issue with the answers provided. The requirement was (emphasis mine), "For each Sales Rep, give me a table with their name (First and Last) and the number of clients they service."

Yeah, I ignored that part of the question because that seems so weird. Why would you need a seperate table for each sales rep

I figured it was a java/sql question, which would require a statement and then a loop. Which, is usually inefficient, but, well. so is homeowrk.