CODEWITHSUNDEEP
sqlsql-server
James Kelleher
James Kelleher

Reputation: 2147

Get row corresponding to a max count in a Group By

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

Answers (1)

Gordon Linoff
Gordon Linoff

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

Related Questions