Re: Select Top 5
- From: "Gary Walter" <forgetit@xxxxxxxxxxxxxxxxx>
- Date: Fri, 15 Sep 2006 17:21:31 -0500
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...
"Michael" wrote:
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
"LTofsrud" wrote:
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
"Michael" wrote:
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;
.
- References:
- RE: Select Top 5
- From: Michael
- RE: Select Top 5
- Prev by Date: Re: GroupBY problem with query
- Next by Date: Re: Select Top 5
- Previous by thread: RE: Select Top 5
- Next by thread: Re: Select Top 5
- Index(es):
Relevant Pages
|
