Reputation: 2147
I have a table of sales broken down into two categories: ZIP Code and Service Type
ZIPCode ServiceID SalePrice
47103 1 10.43
47103 2 8.41
41703 2 52.12
41703 2 13.31
69047 1 5.23
69047 1 14.62
69047 2 51.62
90210 2 85.31
I'd like to transform this into a table that provides, for every ZIP Code, the Service ID with the most sales, and the number of said sales:
ZIPCode ServiceID NumSales
47103 2 3
69047 1 2
90210 2 1
I think I can figure out a way to do this using joins and multiple queries to the table, but is there a way I can just run a GROUP BY
on ZIP Code and retrieve the Service ID that corresponds to the highest COUNT
?
I'm running Microsoft SQL Server, by the way.
Upvotes: 1
Views: 67
Reputation: 1271231
Use row_number()
:
select t.*
from (select zipcode, serviceid, sum(sales) as sales
row_number() over (partition by zipcode order by sum(sales) desc) as seqnum
from t
group by zipcode, serviceid
) t
where seqnum = 1;
If you want ties, then use rank()
or dense_rank()
instead of row_number()
.
Upvotes: 2