OwnThisField OwnThisField - 2 months ago 12
SQL Question

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

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|

Answer

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.