How to select most frequent value in a column per each id group?

Question

I have a table in SQL that looks like this:

user_id | data1
0       | 6
0       | 6
0       | 6
0       | 1
0       | 1
0       | 2
1       | 5
1       | 5
1       | 3
1       | 3
1       | 3
1       | 7

I want to write a query that returns two columns: a column for the user id, and a column for what the most frequently occurring value per id is. In my example, for user_id 0, the most frequent value is 6, and for user_id 1, the most frequent value is 3. I would want it to look like below:

user_id | most_frequent_value
0       | 6
1       | 3

I am using the query below to get the most frequent value, but it runs against the whole table and returns the most common value for the whole table instead of for each id. What would I need to add to my query to get it to return the most frequent value for each id? I am thinking I need to use a subquery, but am unsure of how to structure it.

SELECT user_id, data1 AS most_frequent_value
FROM my_table
GROUP BY user_id, data1
ORDER BY COUNT(*) DESC LIMIT 1

What about draws? (i.e. you add a `(0, 1)` row to your example; then both `6` and `1` are the most frequent value, because both of them occur 3 times). — pozs, Dec 14 '16 at 16:31

score 16 · Answer 1 · edited Nov 10 '20 at 22:27

16

You can use a window function to rank the userids based on their count of data1.

WITH cte AS (
SELECT 
    user_id 
  , data1
  , ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY COUNT(data1) DESC) rn
FROM dbo.YourTable
GROUP BY
  user_id,
  data1)

SELECT
    user_id,
    data1
FROM cte WHERE rn = 1

edited Nov 10 '20 at 22:27

Asclepius

57,944
17
167
143

answered Dec 14 '16 at 16:14

SQLChao

7,709
1
17
32

score 6 · Answer 2 · edited Dec 14 '16 at 16:33

6

If you use proper "order by" then distinct on (user_id) make the same work because it takes 1.line from data partitioned by "user_id". DISTINCT ON is specialty of PostgreSQL.

select distinct on (user_id) user_id, most_frequent_value from (
SELECT user_id, data1 AS most_frequent_value, count(*) as _count
FROM my_table
GROUP BY user_id, data1) a
ORDER BY user_id, _count DESC

edited Dec 14 '16 at 16:33

pozs

34,608
5
57
63

answered Dec 14 '16 at 16:04

JosMac

2,164
1
17
23

may I know why -1? Because I tested it on this examle and I use the same queries often.... – JosMac Dec 14 '16 at 16:06
This works great - thank you! I too would like to know why it had a -1 vote (that wasn't me) – cjh193 Dec 14 '16 at 16:24
what your comment mean in simple English please? As I wrote `DISTINCT ON` is specialty of PostgreSQL - https://www.postgresql.org/docs/current/static/sql-select.html - and it does the same as window function mentioned bellow. – JosMac Dec 14 '16 at 16:38

score 4 · Answer 3 · answered May 05 '17 at 13:53

With postgres 9.4 or greater it is possible. You can use it like:

SELECT 
    user_id, MODE() WITHIN GROUP (ORDER BY value) 
FROM  
    (VALUES (0,6), (0,6), (0, 6), (0,1),(0,1), (1,5), (1,5), (1,3), (1,3), (1,7)) 
    users (user_id, value)
GROUP BY user_id

How to select most frequent value in a column per each id group?

3 Answers3

Linked