Ranking at Multiple Levels on SQL Server

user_5364921 0
Viewed 28

I've read a few ways of doing this, but it does not seem to work for me. I'm trying to pull data that has a Category, Itemcode, and Sales. I'm summing this up for a period of time so that my basic query looks like this:

select 
    Category
    , Itemcode
    , sum(Sales)
    , rank() over (partition by Category order by sum(Sales) desc) as ItemRank
from 
    Sales
group by 
    Category, Itemcode

When I do that, my data looks like this:

enter image description here

What I would like to do is to add another rank that would show the rank of the Category as a whole.

Something like this:

enter image description here

What would the query look like with that added in? I've tried several things, but I can't seem to get it to work.

sql-server sql
1 Answers

Taking a guess here, but I gather you want to rank based on the TotalSales? If so, you can join to a subquery to do the aggregation, then use that in another rank column:

declare @Sales table (Category varchar(25), ItemCode int, Sales int)

insert into @Sales 
values
    ('Category A', 123, 100),
    ('Category A', 234, 125),
    ('Category A', 345,  97),
    ('Category B', 456, 354),
    ('Category B', 567, 85),
    ('Category B', 678, 112)
    
select 
s.Category
, Itemcode
, sum(s.Sales)
, rank() over (partition by s.Category order by sum(Sales) desc) as ItemRank
, dense_rank() over (order by sum(TotalSales) desc) as CategoryRank
from @Sales s
join    (   select Category, sum(Sales) as TotalSales 
            from @Sales 
            group by Category
        ) d
on s.Category = d.Category

group by s.Category, Itemcode
Nathan_37690
Related