Ranking / Top 10 under multiple categories (1 Viewer)

mab9

Registered User
Joined
Oct 25, 2006
Messages
63
In a table I have:

- Category (A, B, C)
- Family (A1, A2, A3, B1, B2, etc)
- Item
- Sales

I'm looking for a way to get the top 10 items in each category + family based on sales (ie: top 10 in A-A1, then A-A2, then A-A3, etc). When I try using the top function, it doesn't treat each category + family combination as unique. Any ideas?
 

ajetrumpet

Banned
Joined
Jun 22, 2007
Messages
5,640
Mab,

I think you're going to have to use more than one query to do this. You can start here for your first query; this will give you the total gross revenue for each item, grouped by Category and Family name....
Code:
SELECT [category], [family], [item], sum([sales]) as [Total Item Revenue]
FROM [table]
GROUP BY [category], [family], [item];
After this, you will have to extract records that satisfy the Category and Family you want to look at, write an ORDER BY [Total Item Revenue] DESC clause and SELECT TOP 10 from that. Once you do this, you can use that query (when you finally get the TOP 10 of the first family and category) over and over again to keep joining records (using the UNION function) you are extracting from the first query that is listed above...

If you're wanting to view all of these at once, you might want to look at creating a crosstab query. I don't think it is what you need, but it might be able to give you results more along the line of what you're wanting.
 
Last edited:

mab9

Registered User
Joined
Oct 25, 2006
Messages
63
From another board, someone linked to this code in an older post. After modifying & testing it, it looks like it works. Any issues with going this route rather than the MS route?

Code:
SELECT  
CategoryID 
, UnitsInStock 
FROM 
Products as x 
WHERE 
UnitsInStock in 
( SELECT  top 3 
  UnitsInStock 
  FROM Products 
  WHERE CategoryID = x.CategoryID 
  ORDER BY UnitsInStock desc 
);
 

pbaldy

Wino Moderator
Staff member
Joined
Aug 30, 2003
Messages
33,646
That looks like method 1 from the first link I gave you. In any case, if it works the way you want I'd use it.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom