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)