Syntax error Get the count of the most frequently occurring values in MySQL?

Get the count of the most frequently occurring values in MySQL?

For this, use the aggregate function COUNT() along with GROUP BY. Let us first create a table −

mysql> create table DemoTable
   (
   Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   Value int
   );
Query OK, 0 rows affected (0.74 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable(Value) values(976);
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable(Value) values(67);
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable(Value) values(67);
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable(Value) values(1);
Query OK, 1 row affected (0.27 sec)
mysql> insert into DemoTable(Value) values(90);
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable(Value) values(1);
Query OK, 1 row affected (0.41 sec)
mysql> insert into DemoTable(Value) values(67);
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable(Value) values(976);
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable(Value) values(90);
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable(Value) values(1);
Query OK, 1 row affected (0.23 sec)
mysql> insert into DemoTable(Value) values(10);
Query OK, 1 row affected (0.09 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

Output

+----+-------+
| Id | Value |
+----+-------+
| 1  | 976   |
| 2  | 67    |
| 3  | 67    |
| 4  | 1     |
| 5  | 90    |
| 6  | 1     |
| 7  | 67    |
| 8  | 976   |
| 9  | 90    |
| 10 | 1     |
| 11 | 10    |
+----+-------+
11 rows in set (0.00 sec)

Following is the query to get the count of the most frequently occurring values in MySQL −

mysql> select Value,COUNT(Value) AS ValueFrequency
   from DemoTable group by Value order by ValueFrequency DESC;

Output

+-------+----------------+
| Value | ValueFrequency |
+-------+----------------+
| 67    | 3              |
| 1     | 3              |
| 90    | 2              |
| 976   | 2              |
| 10    | 1              |
+-------+----------------+
5 rows in set (0.09 sec)
Updated on: 2019-07-30T22:30:26+05:30

1K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements