Select Top 5

[Guest]

Hello,
i'm looking to find the top 5 sales by salesmen for each item sold. below
is my query - it is returning just the top 5 salesmen.

any help would be appreciated:

SELECT TOP 5
Query2.Item,
Query2.Salesmen, Query2.Sales
FROM Query2
ORDER BY Query2.Sales DESC;

 

[Guest]

Michael,

Since I didn't have the same tables as you did, I created something similar
in terms of tables and queries that might be of help. The query that I came
up with that gave me the answer was:

SELECT TOP 5 [FirstName]+" "+[LastName] AS Salesmen, Sum(Sales.OrderAmount)
AS [Sum of Orders]
FROM Salesmen INNER JOIN Sales ON Salesmen.SalesmenIdentifier =
Sales.SalesmenIdentifier
GROUP BY [FirstName]+" "+[LastName]
ORDER BY Sum(Sales.OrderAmount) DESC;

Nothing really special going on here. There is a foreign key in the Orders
table that links back to the Salesmen table. All the query displays is a
concatonation of the First and Last Name of the salesperson and the total of
all their sales.

When I query ALL of the results I get:
Salesmen Sum of Orders
Bill Gates $465.00
Bob Maluga $85.00
John Doe $61.00
Trevor Johnson $40.59
Suzy Smith $38.55
Chris Evans $26.00
Sam Mathers $19.00

When I query the TOP 4 results I get:
Salesmen Sum of Orders
Bill Gates $465.00
Bob Maluga $85.00
John Doe $61.00
Trevor Johnson $40.59
Suzy Smith $38.55

I noticed that you are not totalling the sum of all of the orders... was
that intentional?

HTH and good luck,
LT

 

[Guest]

thank you, but i'm also looking to include the Item sold:

for example using your data below -

item Salesmen Sum of Orders
xbox Bill Gates $465.00
xbox Bob Maluga $85.00
xbox John Doe $61.00
xbox Trevor Johnson $40.59
xbox Suzy Smith $38.55
xbox Chris Evans $26.00
xbox Sam Mathers $19.00

item Salesmen Sum of Orders
office software Bill Gates $200.00
office software Bob Maluga $65.00
office software John Doe $45.00
office software Trevor Johnson $34.59
office software Suzy Smith $38.55
office software Chris Evans $16.00
office software Sam Mathers $9.00

Michael,

Since I didn't have the same tables as you did, I created something similar
in terms of tables and queries that might be of help. The query that I came
up with that gave me the answer was:

SELECT TOP 5 [FirstName]+" "+[LastName] AS Salesmen, Sum(Sales.OrderAmount)
AS [Sum of Orders]
FROM Salesmen INNER JOIN Sales ON Salesmen.SalesmenIdentifier =
Sales.SalesmenIdentifier
GROUP BY [FirstName]+" "+[LastName]
ORDER BY Sum(Sales.OrderAmount) DESC;

Nothing really special going on here. There is a foreign key in the Orders
table that links back to the Salesmen table. All the query displays is a
concatonation of the First and Last Name of the salesperson and the total of
all their sales.

When I query ALL of the results I get:
Salesmen Sum of Orders
Bill Gates $465.00
Bob Maluga $85.00
John Doe $61.00
Trevor Johnson $40.59
Suzy Smith $38.55
Chris Evans $26.00
Sam Mathers $19.00

When I query the TOP 4 results I get:
Salesmen Sum of Orders
Bill Gates $465.00
Bob Maluga $85.00
John Doe $61.00
Trevor Johnson $40.59
Suzy Smith $38.55

I noticed that you are not totalling the sum of all of the orders... was
that intentional?

HTH and good luck,
LT

Hello,
i'm looking to find the top 5 sales by salesmen for each item sold. below
is my query - it is returning just the top 5 salesmen.

any help would be appreciated:

SELECT TOP 5
Query2.Item,
Query2.Salesmen, Query2.Sales
FROM Query2
ORDER BY Query2.Sales DESC;

 

[Guest]

Try this --
SELECT T.Item, T.Orders, T.Salesmen, (SELECT COUNT(*)
FROM [LTofsrud] T1
WHERE T1.Item = T.Item
AND T1.Orders >= T.Orders) AS Rank
FROM LTofsrud AS T
WHERE ((((SELECT COUNT(*)
FROM [LTofsrud] T1
WHERE T1.Item = T.Item
AND T1.Orders >= T.Orders))<6))
ORDER BY T.Item, T.Orders DESC;

 

[Guest]

Sorry, I thought it was too easy - resulting data wrong.

Try this --
SELECT T.Item, T.Orders, T.Salesmen, (SELECT COUNT(*)
FROM [LTofsrud] T1
WHERE T1.Item = T.Item
AND T1.Orders >= T.Orders) AS Rank
FROM LTofsrud AS T
WHERE ((((SELECT COUNT(*)
FROM [LTofsrud] T1
WHERE T1.Item = T.Item
AND T1.Orders >= T.Orders))<6))
ORDER BY T.Item, T.Orders DESC;

Hello,
i'm looking to find the top 5 sales by salesmen for each item sold. below
is my query - it is returning just the top 5 salesmen.

any help would be appreciated:

SELECT TOP 5
Query2.Item,
Query2.Salesmen, Query2.Sales
FROM Query2
ORDER BY Query2.Sales DESC;

 

[Gary Walter]

typically, a "top x over groups" query
uses a primary key which you don't
appear to have here, so....

{untested, and probably inefficient}

SELECT
Query2.Item,
Query2.Salesmen,
Query2.Sales
FROM
Query2
WHERE
(SELECT
COUNT(*)
FROM
Query2 As q
WHERE
q.Item = Query2.Item
AND
q.Sales >= Query2.Sales) <= 5;

what's it doing?

when looking at a record in query2
and deciding whether to return it....

count the number of records in query2
for this item whose sales is greater than
or equal to this record's sales...

if that count is less than or equal to 5,
return it...

if there are more than 5 records in query2
for this item whose sales is greater than
or equal to this record's sales, then it isn't
a "top 5," so don't return it....

may have to rethink if there are ties...

thank you, but i'm also looking to include the Item sold:

for example using your data below -

item Salesmen Sum of Orders
xbox Bill Gates $465.00
xbox Bob Maluga $85.00
xbox John Doe $61.00
xbox Trevor Johnson $40.59
xbox Suzy Smith $38.55
xbox Chris Evans $26.00
xbox Sam Mathers $19.00

item Salesmen Sum of Orders
office software Bill Gates $200.00
office software Bob Maluga $65.00
office software John Doe $45.00
office software Trevor Johnson $34.59
office software Suzy Smith $38.55
office software Chris Evans $16.00
office software Sam Mathers $9.00

Michael,

Since I didn't have the same tables as you did, I created something
similar
in terms of tables and queries that might be of help. The query that I
came
up with that gave me the answer was:

SELECT TOP 5 [FirstName]+" "+[LastName] AS Salesmen,
Sum(Sales.OrderAmount)
AS [Sum of Orders]
FROM Salesmen INNER JOIN Sales ON Salesmen.SalesmenIdentifier =
Sales.SalesmenIdentifier
GROUP BY [FirstName]+" "+[LastName]
ORDER BY Sum(Sales.OrderAmount) DESC;

Nothing really special going on here. There is a foreign key in the
Orders
table that links back to the Salesmen table. All the query displays is a
concatonation of the First and Last Name of the salesperson and the total
of
all their sales.

When I query ALL of the results I get:
Salesmen Sum of Orders
Bill Gates $465.00
Bob Maluga $85.00
John Doe $61.00
Trevor Johnson $40.59
Suzy Smith $38.55
Chris Evans $26.00
Sam Mathers $19.00

When I query the TOP 4 results I get:
Salesmen Sum of Orders
Bill Gates $465.00
Bob Maluga $85.00
John Doe $61.00
Trevor Johnson $40.59
Suzy Smith $38.55

I noticed that you are not totalling the sum of all of the orders... was
that intentional?

HTH and good luck,
LT

Hello,
i'm looking to find the top 5 sales by salesmen for each item sold.
below
is my query - it is returning just the top 5 salesmen.

any help would be appreciated:

SELECT TOP 5
Query2.Item,
Query2.Salesmen, Query2.Sales
FROM Query2
ORDER BY Query2.Sales DESC;

 

[Gary Walter]

sorry Karl, your reply just now shows up for me?
(would not have sent mine if had seen)..

I still haven't seen your original post?

why "wrong?" looks good to me...

Sorry, I thought it was too easy - resulting data wrong.

Try this --
SELECT T.Item, T.Orders, T.Salesmen, (SELECT COUNT(*)
FROM [LTofsrud] T1
WHERE T1.Item = T.Item
AND T1.Orders >= T.Orders) AS Rank
FROM LTofsrud AS T
WHERE ((((SELECT COUNT(*)
FROM [LTofsrud] T1
WHERE T1.Item = T.Item
AND T1.Orders >= T.Orders))<6))
ORDER BY T.Item, T.Orders DESC;

Hello,
i'm looking to find the top 5 sales by salesmen for each item sold.
below
is my query - it is returning just the top 5 salesmen.

any help would be appreciated:

SELECT TOP 5
Query2.Item,
Query2.Salesmen, Query2.Sales
FROM Query2
ORDER BY Query2.Sales DESC;

 

[Guest]

Thank you Karl and Gary for replying.
i'm still having trouble. i cannot get this queries to run.
when i apply the code below and try to run the query, i see the query
calculating in the bottom left of the screen, when then hour glass goes away
- the sql page remains and i dont get a data set returned...

Try this --
SELECT T.Item, T.Orders, T.Salesmen, (SELECT COUNT(*)
FROM [LTofsrud] T1
WHERE T1.Item = T.Item
AND T1.Orders >= T.Orders) AS Rank
FROM LTofsrud AS T
WHERE ((((SELECT COUNT(*)
FROM [LTofsrud] T1
WHERE T1.Item = T.Item
AND T1.Orders >= T.Orders))<6))
ORDER BY T.Item, T.Orders DESC;

Hello,
i'm looking to find the top 5 sales by salesmen for each item sold. below
is my query - it is returning just the top 5 salesmen.

any help would be appreciated:

SELECT TOP 5
Query2.Item,
Query2.Salesmen, Query2.Sales
FROM Query2
ORDER BY Query2.Sales DESC;

 

[Guest]

Did you edit it to use your table name instead of [LTofsrud] as I did?

Thank you Karl and Gary for replying.
i'm still having trouble. i cannot get this queries to run.
when i apply the code below and try to run the query, i see the query
calculating in the bottom left of the screen, when then hour glass goes away
- the sql page remains and i dont get a data set returned...

Try this --
SELECT T.Item, T.Orders, T.Salesmen, (SELECT COUNT(*)
FROM [LTofsrud] T1
WHERE T1.Item = T.Item
AND T1.Orders >= T.Orders) AS Rank
FROM LTofsrud AS T
WHERE ((((SELECT COUNT(*)
FROM [LTofsrud] T1
WHERE T1.Item = T.Item
AND T1.Orders >= T.Orders))<6))
ORDER BY T.Item, T.Orders DESC;

Hello,
i'm looking to find the top 5 sales by salesmen for each item sold. below
is my query - it is returning just the top 5 salesmen.

any help would be appreciated:

SELECT TOP 5
Query2.Item,
Query2.Salesmen, Query2.Sales
FROM Query2
ORDER BY Query2.Sales DESC;

 

[Guest]

i edited it for my query name...then i renamed my query [LTofsrud] to see if
that was the problem.

does it matter that im using a query and not a table as the data source?

Did you edit it to use your table name instead of [LTofsrud] as I did?

Thank you Karl and Gary for replying.
i'm still having trouble. i cannot get this queries to run.
when i apply the code below and try to run the query, i see the query
calculating in the bottom left of the screen, when then hour glass goes away
- the sql page remains and i dont get a data set returned...

Try this --
SELECT T.Item, T.Orders, T.Salesmen, (SELECT COUNT(*)
FROM [LTofsrud] T1
WHERE T1.Item = T.Item
AND T1.Orders >= T.Orders) AS Rank
FROM LTofsrud AS T
WHERE ((((SELECT COUNT(*)
FROM [LTofsrud] T1
WHERE T1.Item = T.Item
AND T1.Orders >= T.Orders))<6))
ORDER BY T.Item, T.Orders DESC;


:

Hello,
i'm looking to find the top 5 sales by salesmen for each item sold. below
is my query - it is returning just the top 5 salesmen.

any help would be appreciated:

SELECT TOP 5
Query2.Item,
Query2.Salesmen, Query2.Sales
FROM Query2
ORDER BY Query2.Sales DESC;

 

[Guest]

A table or query should not matter. Check for hard returns that the posting
and pasting might have added. More than that I do not know.

i edited it for my query name...then i renamed my query [LTofsrud] to see if
that was the problem.

does it matter that im using a query and not a table as the data source?

Did you edit it to use your table name instead of [LTofsrud] as I did?

Thank you Karl and Gary for replying.
i'm still having trouble. i cannot get this queries to run.
when i apply the code below and try to run the query, i see the query
calculating in the bottom left of the screen, when then hour glass goes away
- the sql page remains and i dont get a data set returned...

:

Try this --
SELECT T.Item, T.Orders, T.Salesmen, (SELECT COUNT(*)
FROM [LTofsrud] T1
WHERE T1.Item = T.Item
AND T1.Orders >= T.Orders) AS Rank
FROM LTofsrud AS T
WHERE ((((SELECT COUNT(*)
FROM [LTofsrud] T1
WHERE T1.Item = T.Item
AND T1.Orders >= T.Orders))<6))
ORDER BY T.Item, T.Orders DESC;


:

Hello,
i'm looking to find the top 5 sales by salesmen for each item sold. below
is my query - it is returning just the top 5 salesmen.

any help would be appreciated:

SELECT TOP 5
Query2.Item,
Query2.Salesmen, Query2.Sales
FROM Query2
ORDER BY Query2.Sales DESC;