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

Question

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

So here you need a SQL Query & it has nothing to do with python, removing python tag. — sushanth, Jul 13 '20 at 11:50
@Sushanth first time my question was about how to obtain those values in python from one query with mysql. That i have edited my question before submit and forgot to remove python tag. — sniper, Jul 13 '20 at 12:07

score 0 · Answer 1 · answered Jul 13 '20 at 11:56

0

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

answered Jul 13 '20 at 11:56

ddwolf

91
2
10

oh, thanks for your answer. So basically i need to execute a query for each unique name in part, right ? SELECT value, COUNT(*) AS magnitude FROM table where name = 'nameValue' GROUP BY value ORDER BY magnitude DESC LIMIT 1 – sniper Jul 13 '20 at 12:16
yeah, a lot of sub query, very ugly. Unless MySQL support table expression, like ‘with (select ...) as temp select xxx from temp’. As far as I know, plsql support it, MySQL doesn’t – ddwolf Jul 14 '20 at 12:13

score 0 · Answer 2 · answered Jul 13 '20 at 12:17

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.

answered Jul 13 '20 at 12:17

Krishan Kumar

394
4
13

This solution is not count how many times `value` had occurred for each `name`. I need to find most repeated `value` for each unique `name`. – sniper Jul 13 '20 at 12:23

nvidot · Answer 3 · 2020-07-14T19:11:00.530

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;

I think with your help i am almost there. Your queries gave me error: [Err] 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'groups.value' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by But i understood your opinion. Your queries results me count for each channel and name, but i need only the max one. I think the solution is your query and some join table. — sniper, Jul 13 '20 at 14:01
The first query already gives you the max for each name. The third takes care not to use non-aggregated column and incidentally gives all the max for a given name. The use of DISTINCT seems redundant with the use of GROUP BY and I suspect MAX(t2.value) to not be safe. Hope it helps. — nvidot, Jul 15 '20 at 15:40

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

3 Answers3