CODEWITHSUNDEEP
sqlsql-server
tomdemaine
tomdemaine

Reputation: 758

Find the most common value in a particular group

I have a query that I need to modify. It currently has a row for each reference with a 15 min block of time and the account number associated. My goal is to have one row per 15 min block which states which account number occurred most often during that block. 

    SELECT
left(cast(Concat([Hour],':',[Minute]) as time),5) as [Time Block]
,[Reference]
,[Account]

from(

    SELECT 
DATEPART(hh,[Start Time]) as [Hour]
,[Reference]
,case when DATEPART(mi,[Start Time]) between 00 and 15 then '00'
when DATEPART(mi,[Start Time]) between 15 and 30 then '15'
        when DATEPART(mi,[Start Time]) between 30 and 45 then '30'
        when DATEPART(mi,[Start Time]) between 45 and 100 then '00'
        else 'Error' end AS [Minute]

      ,[Account]

  FROM [iPR].[dbo].[InboundCallsView]

  where [Start Time] between '2017-03-21' and '2017-03-22')T

  order by [Time Block]

This gives the output

output

I don't need the reference numbers but I am after

00:00 310523

00:15 310523

00:30 310523

and so on displaying the account with the most rows for each 15 mins in the 24 hour period. Is this possible?

Upvotes: 2

Views: 501

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

This value is called the mode in statistics. It is easy to calculate:

with cte as (<your query here>)
select timeblock, account
from (select timeblock, account, count(*) as cnt,
             row_number() over (partition by timeblock order by count(*) desc) as seqnum
      from cte
      group by timeblock, account
     ) t
where seqnum = 1;

In the event of ties for the most common, this returns one value arbitrarily. If you want all of them, then use rank() or dense_rank().

Upvotes: 5

Related Questions