Often you may need to find most frequent column value in MySQL. In this article, we will learn how to do this using GROUP BY clause and COUNT aggregation function. We will use them to obtain frequency distribution table of unique column values and then select the top most row from it.
How to Find Most Frequent Column Value in MySQL
Let us say you have the MySQL table sales(id, created_at, product, amount)
mysql> create table sales(id int, created_at date, product varchar(5), amount int); mysql> inert into sales(id, created_at, product, amount) values(1, '2022-01-01', 'ABC', 180), (2, '2022-01-02', 'DEF', 150), (3, '2022-01-03', 'ABC', 280), (4, '2022-01-04', 'ABC', 380),
Here is the SQL query to get most frequent column value in MySQL. Replace <column_name> with the column name whose most frequent value you want, and <my_table> with its table name.
SELECT <column_name>, COUNT(<column_name>) AS `value_occurrence` FROM <my_table> GROUP BY <column_name> ORDER BY `value_occurrence` DESC LIMIT 1;
In the above query, we group by each unique value of column using GROUP BY clause, and calculate a count of rows for each unique value using count() function. We order these values in descending order of counts using ORDER BY … DESC clause. This will sort each unique value of column in descending order of its row counts. Finally, we pick the topmost row (most frequent value) using LIMIT 1.
Please note, if there are two or more values that are most frequent then you will need to select that many rows by changing your LIMIT clause. For example, if there are 3 most frequent values then you need to use LIMIT 3. So it is better to first run the above query without LIMIT clause and then select the required number of rows from top.
SELECT <column_name>, COUNT(<column_name>) AS `value_occurrence` FROM <my_table> GROUP BY <column_name> ORDER BY `value_occurrence` DESC
Applying the above queries to our table, if you want to find the most frequently purchased product using sales table, you can use the following query to find most frequent value of product column.
SELECT product, COUNT(product) AS `value_occurrence` FROM sales GROUP BY product ORDER BY `value_occurrence` DESC LIMIT 1; product | value_occurrence -------------------------- ABC | 3
In this article, we have learnt how to find most frequent column value in MySQL.
How to Truncate Foreign Key Constrained Table
How to Export MySQL Schema Without Data
How to Insert Element After Another Element in JS
How to Sum Values of JS Object
How to Access POST Form Fields in ExpressJS