Count repeated value from a column for each row with same value in mysql

+------+-------+ | name | value | +======+=======+ | 5    | 0     | +------+-------+ | 4    | 0     | +------+-------+ | 3    | 1     | +------+-------+ | 4    | 1     | +------+-------+ | 4    | 1     | +------+-------+ | 5    | 0     | +------+-------+              

I want to obtain the most repeated value for each name in part.
name 5 have the most repeated value 0
name 4 have the most repeated value 1
name 3 have the most repeated value 1
How can i do that in a single query to mysql ?
Thanks

SOLVED

With the select statement from @nvidot and another posts from SO, i found this is a common problem with this type of query. Newer versions of MySQL come with ONLY_FULL_GROUP_BY enabled by default, and many of the solutions here will fail in testing with this condition.

So the working formula for me was:

SELECT DISTINCT t1.name, MAX(t1.occ), MAX(t2.value) FROM (select name, value, count(*) as occ from `table` group by name, value order by occ desc) AS t1 JOIN (select name, adstatus, count(*) as occ from `table` group by name, value order by occ desc) AS t2 ON t2.name = t1.name AND t2.occ = (     SELECT MAX(occ) FROM (select name, value, count(*) as occ from `table` group by name, value order by occ desc) t3 WHERE t3.name = t1.name ) GROUP BY t1.name; 
Add Comment
3 Answer(s)

In Oracle’s PL/Sql, there is a specific feature can satisfy your request which is called Window function, but in MySql, there is no such thing untile mysql-8.0

Add Comment
SELECT       `column`,              COUNT(`column`) AS `value_occurrence`      FROM     `my_table`     GROUP BY `column`     ORDER BY `value_occurrence` DESC 

Also please visit this link to more clear.

Add Comment
select name, val from (select name, val, max(occ)       from (select name, val, count(*) as occ             from `sample` group by name, val             order by occ desc) as groups       group by name) as maximums; 

Outer most select serves as cosmetic to display only name and val order by occ desc serves to obtain the correct val

The following might be sufficient:

select name, val from (select name, val, count(*) as occ       from `sample`       group by name, val       order by occ desc) as groups group by name; 

[edit]: The following should not trigger error as it does not use non-aggregate column and it does not rely on order by. Multiple name rows might exists if there exists multiples maxs for name/val count.

select name,val       from (select name as maxname, max(occ) as maxocc             from (select name, val, count(*) as occ                   from `sample`                   group by name, val) as counts2             group by name) as maxs       join (select name, val, count(*) as numocc             from `sample`             group by name, val) as counts1       on name = maxname AND numocc = maxocc; 
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.