How to Select the Most Frequently Appearing Values

how to select the most frequently appearing values?

select
  x.last_name,
  x.name_count
from
  (select
    u.last_name,
    count(*) as name_count,
    rank() over (order by count(*) desc) as rank
  from
    users u
  group by
    u.last_name) x
where
  x.rank = 1

Use the analytical function rank. It will assign a numbering based on the order of count(*) desc. If two names got the same count, they get the same rank, and the next number is skipped (so you might get rows having ranks 1, 1 and 3). dense_rank is an alternative which doesn't skip the next number if two rows got the same rank, (so you'd get 1, 1, 2), but if you want only the rows with rank 1, there is not much of a difference.

If you want only one row, you'd want each row to have a different number. In that case, use row_number. Apart from this small-but-important difference, these functions are similar and can be used in the same way.

Find most frequent value in SQL column

SELECT
  <column_name>,
  COUNT(<column_name>) AS `value_occurrence` 

FROM
  <my_table>

GROUP BY 
  <column_name>

ORDER BY 
  `value_occurrence` DESC

LIMIT 1;

Replace <column_name> and <my_table>. Increase 1 if you want to see the N most common values of the column.

How to select most common values in a column

starwars %>%
  count(homeworld, sort = TRUE) %>%
  slice(1:2) %>%
  left_join(starwars)

Result

Joining, by = "homeworld"
# A tibble: 21 x 15
   homeworld     n name         height  mass hair_color skin_color  eye_color birth_year sex    gender    species films    vehicles starships
   <chr>     <int> <chr>         <int> <dbl> <chr>      <chr>       <chr>          <dbl> <chr>  <chr>     <chr>   <list>   <list>   <list>   
 1 Naboo        11 R2-D2            96    32 NA         white, blue red               33 none   masculine Droid   <chr [7… <chr [0… <chr [0]>
 2 Naboo        11 Palpatine       170    75 grey       pale        yellow            82 male   masculine Human   <chr [5… <chr [0… <chr [0]>
 3 Naboo        11 Jar Jar Bin…    196    66 none       orange      orange            52 male   masculine Gungan  <chr [2… <chr [0… <chr [0]>
 4 Naboo        11 Roos Tarpals    224    82 none       grey        orange            NA male   masculine Gungan  <chr [1… <chr [0… <chr [0]>
 5 Naboo        11 Rugor Nass      206    NA none       green       orange            NA male   masculine Gungan  <chr [1… <chr [0… <chr [0]>
 6 Naboo        11 Ric Olié        183    NA brown      fair        blue              NA NA     NA        NA      <chr [1… <chr [0… <chr [1]>
 7 Naboo        11 Quarsh Pana…    183    NA black      dark        brown             62 NA     NA        NA      <chr [1… <chr [0… <chr [0]>
 8 Naboo        11 Gregar Typho    185    85 black      dark        brown             NA male   masculine Human   <chr [1… <chr [0… <chr [1]>
 9 Naboo        11 Cordé           157    NA brown      light       brown             NA female feminine  Human   <chr [1… <chr [0… <chr [0]>
10 Naboo        11 Dormé           165    NA brown      light       brown             NA female feminine  Human   <chr [1… <chr [0… <chr [0]>
# … with 11 more rows

Select most common value from a field in MySQL

You need to group by the interesting column and for each value, select the value itself and the number of rows in which it appears.

Then it's a matter of sorting (to put the most common value first) and limiting the results to only one row.

In query form:

SELECT column, COUNT(*) AS magnitude 
FROM table 
GROUP BY column 
ORDER BY magnitude DESC
LIMIT 1

SQL Select most common values

This will do:

select age from persons
group by age
having count(*) = (
  select count(*) from persons
  group by age
  order by count(*) desc
  limit 1)

Pandas get the most frequent values of a column

By using mode

df.name.mode()
Out[712]: 
0     alex
1    helen
dtype: object

---

Related Topics