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;
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
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.
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;