SELECT most frequent combo of values by Group

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SELECT most frequent combo of values by Group

Author

Topic

rsklein
Starting Member

1 Post

Posted - 2011-10-12 : 09:45:47

Say I have a table of sales with the following fields:

Custno - Customer number (unique key for this table)
Regn - The sales region that this customer is located in
Sales - 2010 sales $ for this customer
SRepNo - The Sale Rep # of the sales rep for this customer
TRepNo - The Tech Rep # of the tech rep for this customer

What I'm trying to figure out his how construct a table with the
most frequently occurring Sales Rep/Tech Rep combination for each
region. Also, each record in the result needs to show the total sales
handled by that Sales Rep/Tech Rep combination in that region and
the number of customers handled by that Sales Rep/Tech Rep
combination in that region.

(In the case of a tie, I would still only want a single record per region and would be happy with any random selection among the
Sales Rep/ Tech Rep combos that tied for most frequent.)

Nothing I've tried seems to work quite right. Any help would be much appreciated! Thank you!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-12 : 09:56:23

[code]
; with data as
(
select Regn, SRepNo, TRepNo, TotSales = sum(Sales), CustCount = count(distinct Custno)
from yourtable
group by Regn, SRepNo, TRepNo
),
data2 as
(
select *, row_no = row_number() over (partition by Regn order by TotSales desc)
from data
)
select *
from data2
where row_no = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources