phpmyadmin Question posted in
The official documentation can be found here.

How to find the most common value across multiple columns – Phpmyadmin

I was just wondering what MySQL code could I use to find the most common value across a series of columns containing similar values.

SELECT       `column`,
             COUNT(`column`) AS `value_occurrence` 
    FROM     `my_table`
    GROUP BY `column`
    ORDER BY `value_occurrence` DESC
    LIMIT    1;

I know I can use the above code to the find most common value in a single column but how would I expand this to find the value in 3 columns?

Eg

Column 1: 1, 2, 2, 4

Column 2: 1, 3, 2, 1

Column 3: 1, 2, 2, 2

Result : 2

Tags:

2

Answers


  1. 0

    Use UNION to move all the columns into a single column.

    SELECT col, COUNT(*) AS value_occurrence
FROM (
    SELECT col1 AS col
    FROM my_table
    UNION ALL
    SELECT col2 AS col
    FROM my_table
    UNION ALL
    SELECT col3 AS col
    FROM my_table) AS x
GROUP BY column
ORDER BY value_occurrence DESC
LIMIT 1
    Login or Signup to reply.
  2. 0

    All you have to do is replace column in COUNT(column) with an asterisk (*). That is the universal selector for accessing all records of a table. Then in your ORDER BY clause, specify allColumns as the scope of the data you want to order it by.

    select allColumns, count(*) as value_occurrence from dataTable
group by allColumns
order by value_occurrence desc, allColumns
LIMIT 1;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search