find MODE (with groupings) in T-SQL

[Homerboy]

I'm trying to find the MODE for a set of data.
However I need it group by a specific column -- meaning I want to run my query and have multiple rows returned showing the MODE of the data in question for each row.

The query is this:

select
TOP 1
master.VENUE1_NO,
COUNT(*) as 'frequency',

field

from master inner join INFINITY on master.FILENO = infinity.FILENO
where CODE = '*lgldt'
and master.FORW_NO IN (2256, 2356, 2357, 2358, 2359)
and FIELD <= GETDATE()
--and DATEDIFF(MONTH,FIELD,GETDATE()) <= 4
and field is not null
group by master.VENUE1_NO, FIELD
order by COUNT(*) DESC

What that will currently give me is ONE row of data.
What I want is a row for EACH master.VENUE1_NO and the MODE per master.VENUE1_NO

I just can not wrap my brain around this.

***WARNING***
once I get this, I will need to tackle the same thing for MEDIAN.


Thanks in advance.

 

[Homerboy]

I did some more google digging.
I guess what I really want to do here is "Top n Per Group"
But, admittedly, I'm having issues wrapping my head around it still.

 

[Homerboy]

grrr

 

[uclabachelor]

I'm trying to find the MODE for a set of data.
However I need it group by a specific column -- meaning I want to run my query and have multiple rows returned showing the MODE of the data in question for each row.

The query is this:

select
TOP 1
master.VENUE1_NO,
COUNT(*) as 'frequency',

field

from master inner join INFINITY on master.FILENO = infinity.FILENO
where CODE = '*lgldt'
and master.FORW_NO IN (2256, 2356, 2357, 2358, 2359)
and FIELD <= GETDATE()
--and DATEDIFF(MONTH,FIELD,GETDATE()) <= 4
and field is not null
group by master.VENUE1_NO, FIELD
order by COUNT(*) DESC

What that will currently give me is ONE row of data.
What I want is a row for EACH master.VENUE1_NO and the MODE per master.VENUE1_NO

I just can not wrap my brain around this.

***WARNING***
once I get this, I will need to tackle the same thing for MEDIAN.


Thanks in advance.

You'd have to do sub query to first group by the field, then an outer query to join and order.

SELECT TOP N a.field, b.* FROM a INNER JOIN (SELECT primary_key, COUNT(*)
 AS count FROM b GROUP BY primary_key) AS b ON a.primary_key=b.primary_key 
ORDER BY b.count DESC

 

[KentState]

Use the RANK() function inside a CTE and select what you are looking from there. You can use multiple RANK() functions in a statement with various groupings.