MySql : retrieve most occurring value in column for another column

Question

I'm looking for a way in MySql to select the most occurring value in one column.

E.g. this table

+-------+-------+
|  Name | Signin|
+-------+-------+
| Name1 |   1   |
| Name1 |   2   |
| Name1 |   1   |
| Name1 |   3   |
| Name1 |   2   |
| Name1 |   2   |
| Name2 |   4   |
| Name2 |   5   |
| Name2 |   5   |
| Name2 |   5   |
| Name2 |   5   |
| Name2 |   6   |
+-------+-------+

The column Signin has two=1, three=2, one=3 sow for the Name1 the most recent value is 2, for the Name2 the most recent value is 5 as in the column Signin are one=4, four=5, one=6

The result I need is like this

Name1 - 2
Name2 - 5

Possible duplicate of [Using Count to find the number of occurrences](http://stackoverflow.com/questions/7654978/using-count-to-find-the-number-of-occurrences) — Ishita Sinha, Aug 09 '16 at 03:57

score 1 · Accepted Answer · answered Aug 09 '16 at 00:25

What you are looking for is called the "mode" in statistics. Most databases support with CTEs or window functions which simplify the calculation. If your data is not too big, I think this following aggregation trick is the easiest method:

select name,
       substring_index(group_concat(signin order by cnt desc), ',', 1) as signin
from (select name, signin, count(*) as cnt
      from t
      group by name, signin
     ) ns
group by name;

The above does not always work. One possibility is sgeddes's approach using two aggregations. Another is to use variables:

select ns.*
from (select ns.*,
             (@rn := if(@n = name, @rn + 1,
                        if(@n := name, 1, 1)
                       )
             ) as rn
      from (select name, signin, count(*) as cnt
            from t
            group by name, signin
            order by name, count(*) desc
           ) ns cross join
           (select @n := '', @rn := 0) params
     ) ns
where seqnum = 1;

Thank you for the answer its doing the job. All the best – user3255412 Aug 09 '16 at 00:34 — user3255412, Aug 09 '16 at 00:34

MySql : retrieve most occurring value in column for another column

1 Answers1