1. Registered User
Join Date
Mar 2013
Posts
25

I am trying to solve the following problem:

Find the largest order amount for each salesperson and the associated order number, along with the customer to whom that order belongs to. Right now all my code does is:

Display salesperson_id more than once and display all order number and all sales amounts. What it should be doing is giving me only 1 salesperson_id with the highest sales amount and include the order number along with it.

Any help would be greatly appreciated.

select distinct orders.salesperson_id, Number as OrderNum, Amount
from
Orders
left JOIN
(
SELECT salesperson_id, max(number) As MaxNum, MAX(Amount) AS MaxOrder
FROM Orders
GROUP BY salesperson_id, Number, Amount
) as TopOrderAmountsPerSalesperson
On Orders.cust_id = TopOrderAmountsPerSalesperson.salesperson_id
group by orders.salesperson_id, Number, Amount

2. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
First write a query that will become your sub-query. It needs to return the salesperson_id and the amount of each salesperson's largest order amount.

-PatP

3. Registered User
Join Date
Mar 2013
Posts
25

## subquery re write

Hi PatP,
Ok, here is my new subquery:

(SELECT salesperson_id, MAX(Amount) AS MaxOrder
FROM Orders
GROUP BY salesperson_id, Amount)

I am a newbie at this so i sure appreciate the help
I tested the query, it returns numerous salesperson_id's and the maximum order associated with each of the references to salesperson_id's.

What would you suggest next?

AndrewC

4. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Now take that query (as a sub-query) and do an INNER JOIN against your main table on both the salesperson_id AND the transaction amount.

There is still a potential "gotcha", but this will have you 99% complete and we'll see if your teacher was smart (or evil) enough to include data that will expose the gotcha or not.

Oooo... I missed another "gotcha". Re-think your query a bit, you've got a problem in it.

-PatP

5. Registered User
Join Date
Mar 2013
Posts
25

## The source data

Hi PatP,
Well i am actually done with my class at the community college. They only went as far as intermediate sql. The problem is that in the work world the queries are more difficult than what is in school. For that reason i look online for tests and problems to improve my skills.

I am not trying to get you to do my homework. Hehehe. Here is the actual website where the data was found:

Well here is my sql code =>

select Orders.salesperson_id, salesperson.Name, Orders.Number, Orders.Amount
from salesperson, Customer
Inner join
(SELECT salesperson_id, MAX(Amount) AS MaxOrder
FROM Orders
group by salesperson_id) as TopOrderAmountsPerSalesperson

As you can see i sometimes have a problem with syntax and dont know what to do. That is currently one of my problems. But you get better with practice and that is what i am trying to do!

AndrewC

6. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Look at the output of your query for the results for Dan. There should only be one "largest" value for a given sales person.

-PatP

7. Registered User
Join Date
Mar 2013
Posts
25
The subquery is here:
SELECT salesperson_id, MAX(Amount) AS MaxOrderAmount
FROM Orders
GROUP BY salesperson_id

From this i now get only one instance of a salesperson_id that has only the MaxOrderAmount. This is what i originally wanted.

8. Registered User
Join Date
Mar 2013
Posts
25

## Output of subquery

Here is my output of the subquery.
I did check the data in my DB and i do have Dan for max order amount at 720 so its good. Unless i made a typo or change to the original table?

salesperson_id MaxOrderAmount

1 460
2 2400
7 720
8 1800

9. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Very good! That's the problem that I caught that needed to be fixed.

I'm trying to lead you through the process of discovery... Helping you find the answers (just like you'd have to do at work), without actually giving you those answers. This isn't quite like learning on your own, but it allows you to cover things a lot faster if you have someone experienced to give you a nudge in the right direction!

Now that you've got the sub-query correct, you can use it with an INNER JOIN to pare down the list of orders to get the other information that the exercise requires. Give it a shot and come back with questions!

-PatP

10. Registered User
Join Date
Mar 2013
Posts
25

## Onward again to join subquery with inner join

Hi PatP,
Well i hope that this dialog helps others in my situation as well?

Here is what i have now. There are problems with it because i am lost on syntax

Normally i do something like:
select column1, column2
from table A
inner joint B
ON tableA.id = tableB.id
where (expression a>b or something)

And for subqueries:
SELECT ProductID, ProductName, InStock, OnOrder
FROM Products
WHERE InStock > ALL
(SELECT InStock
FROM Products
WHERE InStock = 10);

I have never combined a subquery and a inner join....help!?

My code:

select Orders.salesperson_id, Customer.Name, Orders.Number, Orders.Amount
from customer
INNER JOIN
(SELECT salesperson_id, MAX(Amount) AS MaxOrderAmount
FROM Orders
GROUP BY salesperson_id)

AndrewC

11. Registered User
Join Date
Mar 2013
Posts
25

## Thank you again

Hi PatP,
Thank you again for your insight and help. Your help and style is really appreciated. I forgot to add that in my last post. I am really sorry. You are very insightful and a great mentor.

AndrewC

12. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Code:
```select Orders.salesperson_id, Customer.Name, Orders.Number, Orders.Amount
FROM (
SELECT salesperson_id, MAX(Amount) AS MaxOrderAmount
FROM Orders
GROUP BY salesperson_id
) AS da_max  -- da_max alias is REALLY important in this problem
INNER JOIN Orders
ON <<what goes here???>>
INNER JOIN Customers
ON <<what goes here???>>```
-PatP

13. Registered User
Join Date
Mar 2013
Posts
25

## Having troubles

Hi PatP,

Here is what i have but i am stuck again....
I know that after ON the expression will have different tables but the columns will be the same name. For example:
ON orders.salesperson_id = TopOrderAmountsPerSalesperson.salesperson_id

select Orders.salesperson_id, Customer.Name, Orders.Number, Orders.Amount
FROM (
SELECT salesperson_id, MAX(Amount) AS MaxOrderAmount
FROM Orders
GROUP BY salesperson_id
) AS TopOrderAmountsPerSalesperson
INNER JOIN Orders
ON orders.salesperson_id = TopOrderAmountsPerSalesperson.salesperson_id
INNER JOIN Customer
ON Customer.ID = ????

14. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
The ON clause can contain any true/false condition (this is called a Boolean expression). While you might have only seen cases where the column names were identical and compared for equality (and that is the general case), the only requirement is that it be a Boolean expression.

Look in the columns of the Orders table for the answer to your question.

-PatP

15. Registered User
Join Date
Mar 2013
Posts
25

## Talking out loud to share what is in my head...

Hi PatP,
So i see this section:

select Orders.salesperson_id, Customer.Name, Orders.Number, Orders.Amount
=comment = this is only using orders and customer table so it looks like i only need to join these 2 tables from what we have done so far?

The next section of code looks like a derived table because the from is this snippet of code. I just learned about derived tables. See below:

SELECT salesperson_id, MAX(Amount) AS MaxOrderAmount
FROM Orders
GROUP BY salesperson_id
) AS TopOrderAmountsPerSalesperson

You have a hint directing me to the orders table and i am trying to figure out what that means exactly? and how that ties in and where?

The derived table is pulling data from orders table. The two select statements refer to the orders table. The the confusing part is that here is an inner join again to the orders table. But i suppose in the 2nd select you can have another select with and orders table reference.

So now if i try to fill in the 1st inner join question:

Joins cant be joined on anything but say natural keys or foreign keys. In this case the only foreign key to use in orders table is cust_id or salesperson_id. Since we are not connecting to the customer table i wont use cust_id. So i should have:

ON orders.salesperson_id = ??
Now i am wondering what goes on the other side? I thought that th FROM (..)
the code inside the from is another table. The code i am talking about is:

(
SELECT salesperson_id, MAX(Amount) AS MaxOrderAmount
FROM Orders
GROUP BY salesperson_id
) AS TopOrderAmountsPerSalesperson

So this new table is, TopOrderAmountsPerSalesperson.
So finally we have:

ON orders.salesperson_id = TopOrderAmountsPerSalesperson.salesperson_id

How am i doing so far?

Than i have to handle the last bit of code:

INNER JOIN Customer
ON Customer.ID = ????

Here is the customer table:

customer
---------
ID
Name
City
Industry_Type

The only foreign key is ID so that is why i used "ON Customer.ID = ??"

I still havent figured out what to do with the "??" yet.
The only table i have not joined is the customer table so i should have then:

ON Customer.ID = Orders.salesperson_id

The reason i picked orders.salesperson_id and not orders.cust_id is that i am never referencing orders.cust_id in my query but i am referencing orders.salesperson_id.

Ok, how am i doing with my logic?

Here is my complete code:

select Orders.salesperson_id, Customer.Name, Orders.Number, Orders.Amount
FROM (
SELECT salesperson_id, MAX(Amount) AS MaxOrderAmount
FROM Orders
GROUP BY salesperson_id
) AS TopOrderAmountsPerSalesperson
INNER JOIN Orders
ON orders.salesperson_id = TopOrderAmountsPerSalesperson.salesperson_id
INNER JOIN Customer
ON Customer.ID = Orders.salesperson_id

ps: The only problem is that this code doesnt produce the correct result but at least i showed my logic

AndrewC

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•