TSQL mode (as in mean, median,mode)
10,757
The mode is the most common value. You can get this with aggregation and row_number()
:
select idsOfInterest, valueOfInterest
from (select idsOfInterest, valueOfInterest, count(*) as cnt,
row_number() over (partition by idsOfInterest order by count(*) desc) as seqnum
from table t
group by idsOfInterest, valueOfInterest
) t
where seqnum = 1;
Author by
Michael Plazzer
Physicist come cross-industry data scientist at AIA Australia. Data science blog Academic papers
Updated on June 19, 2022Comments
-
Michael Plazzer about 2 years
I'm trying to calculate the mode of a series of idsofInterest in a table, each with an accompanying valueOfInterest such:
idsOfInterest | valueOfInterest 2 | 1A 2 | 1A 2 | 3B 1 | 2A 1 | 2C 1 | 2A 4 | 3B 4 | 3B 4 | 4C
but with millions of rows.
Each list of idOfInterest is sufficiently long that multimodes are not a problem. Ideally, I would like something likeidsOfInterest | modeValueOfInterest 1 | 2A 2 | 1A 3 | 3C 4 | 3B