Ranking / Top 10 under multiple categories (1 Viewer)

[mab9]

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]

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....

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.

 

[pbaldy]

Maybe these will help:

http://support.microsoft.com/kb/210039/en-us

http://support.microsoft.com/kb/208822/en-us

 

[ajetrumpet]

Yeah right Paul...

I wish I was a programmer, so I could read that junk!!

 

[mab9]

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?

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]

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.