MySQL query to display record with maximum count values in a group with other column values?

For this, use the GROUP BY HAVING clause. Let us first create a table −

mysql> create table DemoTable
(
   Value int
);
Query OK, 0 rows affected (0.54 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values(88);
Query OK, 1 row affected (0.22 sec)
mysql> insert into DemoTable values(88);
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable values(88);
Query OK, 1 row affected (0.09 sec)
mysql> insert into DemoTable values(99);
Query OK, 1 row affected (0.09 sec)
mysql> insert into DemoTable values(99);
Query OK, 1 row affected (0.09 sec)
mysql> insert into DemoTable values(99);
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable values(99);
Query OK, 1 row affected (0.07 sec)
mysql> insert into DemoTable values(100);
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values(100);
Query OK, 1 row affected (0.07 sec)
mysql> insert into DemoTable values(88);
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable values(88);
Query OK, 1 row affected (0.11 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+-------+
| Value |
+-------+
|    88 |
|    88 |
|    88 |
|    99 |
|    99 |
|    99 |
|    99 |
|   100 |
|   100 |
|    88 |
|    88 |
+-------+
11 rows in set (0.00 sec)

Following is the query to display records with maximum count values in a group with other columns. Here, we have repeated values in a column and we are ordering the values. After ORDER BY DESC, we are fetching the first value and displaying its count in a new column “NumberOfCount” −

mysql> select Value,count(*) as NumberOfCount
   from DemoTable
   group by Value
   having count(*)=(select count(*) as NumberOfCount
      from DemoTable
      group by Value
      order by NumberOfCount desc
      limit 1);

This will produce the following output −

+-------+---------------+
| Value | NumberOfCount |
+-------+---------------+
|    88 |             5 |
+-------+---------------+
1 row in set (0.00 sec)

AmitDiwan

Updated on 04-Oct-2019 06:40:23

• Related Questions & Answers
• MySQL query to select a record with two exact values?
• MySQL query to display only the column values with corresponding column having whitespace
• Get the maximum count of distinct values in a separate column with MySQL
• MySQL query to group by column and display the sum of similar values in another column
• MySQL query to group results by date and display the count of duplicate values?
• Shuffling column values with MySQL?
• Getting maximum from a column value and set it for all other values in the same column with MySQL?
• MySQL query to count the duplicate ID values and display the result in a separate column
• MySQL query to count occurrences of distinct values and display the result in a new column?
• MySQL query to group by names and display the count in a new column
• GROUP BY and display only non-empty column values in MySQL
• MySQL query to count number of duplicate values in a table column
• Display MySQL histogram with negative values?
• MongoDB query to concatenate values of array with other fields
• MySQL query to retrieve only the column values with special characters?