SQL query for finding the most frequent value of a grouped by value

Question

I'm using SQLite browser, I'm trying to find a query that can find the max of each grouped by a value from another column from:

Table is called main

|  |Place  |Value|
| 1| London|  101|
| 2| London|  20|
| 3| London|  101|
| 4| London|  20|
| 5| London|  20|
| 6| London|  20|
| 7| London|  20|
| 8| London|  20|
| 9| France|  30|
| 10| France|  30|
| 11| France|  30|
| 12| France|  30|

The result I'm looking for is the finding the most frequent value grouping by place:

|  |Place  |Most Frequent Value|
| 1| London|   20|
| 2| France|   30|

Or even better

|  |Place  |Most Frequent Value|Largest Percentage|2nd Largest Percentage|
| 1| London|   20|   0.75|   0.25|
| 2| France|   30|   1|   0.75|

score 4 · Accepted Answer · edited Sep 11 '16 at 05:34

You can group by place, then value, and order by frequency eg.

select place,value,count(value) as freq from cars group by place,value order by place, freq;

This will not give exactly the answer you want, but near to it like

London | 101 | 2
France | 30  | 4
London | 20  | 6

Now select place and value from this intermediate table and group by place, so that only one row per place is displayed.

select place,value from
(select place,value,count(value) as freq from cars group by place,value order by place, freq)
group by place;

This will produce the result like following:

France | 30
London | 20

This works for sqlite. But for some other programs, it might not work as expected and return the place and value with least frequency. In those, you can put order by place, freq desc instead to solve your problem.

Thank you! This didnt to give me the most frequent value but the least frequent, so i changed to ASC from DESC and was all good. — OwnThisField, Sep 11 '16 at 14:58

score 0 · Answer 2 · answered Sep 11 '16 at 04:57

The first part would be something like this.

http://sqlfiddle.com/#!7/ac182/8

with tbl1 as 
(select a.place,a.value,count(a.value) as val_count 
from table1 a
group by a.place,a.value
)
select t1.place,
    t1.value as most_frequent_value 
from tbl1 t1
inner join 
    (select place,max(val_count) as val_count from tbl1
            group by place) t2
on t1.place=t2.place 
    and t1.val_count=t2.val_count

Here we are deriving tbl1 which will give us the count of each place and value combination. Now we will join this data with another derived table t2 which will find the max count and we will join this data to get the required result.

I am not sure how do you want the percentage in second output, but if you understood this query, you can use some logic on top of it do derive the required output. Play around with the sqlfiddle. All the best.

score 0 · Answer 3 · answered Jul 14 '22 at 18:15

RANK

SQLite now supports RANK, so we can use the exact same syntax that works on PostgreSQL, similar to https://stackoverflow.com/a/12448971/895245

SELECT "city", "value", "cnt"
  FROM (
    SELECT
      "city",
      "value",
      COUNT(*) AS "cnt",
      RANK() OVER (
        PARTITION BY "city"
        ORDER BY COUNT(*) DESC
      ) AS "rnk"
    FROM "Sales"
    GROUP BY "city", "value"
  ) AS "sub"
WHERE "rnk" = 1
ORDER BY
  "city" ASC,
  "value" ASC

This would return all in case of tie. To return just one you could use ROW_NUMBER instead of RANK.

Tested on SQLite 3.34.0 and PostgreSQL 14.3. GitHub upstream.

SQL query for finding the most frequent value of a grouped by value

3 Answers3

Linked