Counting common elements in MySQL?

To count common elements, use COUNT() and GROUP BY. Let us first create a table −

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

Insert some records in the table using insert command −

mysql> insert into DemoTable(Number) values(10); Query OK, 1 row affected (0.06 sec) mysql> insert into DemoTable(Number) values(20); Query OK, 1 row affected (0.04 sec) mysql> insert into DemoTable(Number) values(20); Query OK, 1 row affected (0.04 sec) mysql> insert into DemoTable(Number) values(30); Query OK, 1 row affected (0.04 sec) mysql> insert into DemoTable(Number) values(10); Query OK, 1 row affected (0.05 sec) mysql> insert into DemoTable(Number) values(10); Query OK, 1 row affected (0.04 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+----+--------+ | Id | Number | +----+--------+ | 1 | 10 | | 2 | 20 | | 3 | 20 | | 4 | 30 | | 5 | 10 | | 6 | 10 | +----+--------+ 6 rows in set (0.00 sec)

Following is the query to count common elements −

mysql> select Number,count(Number) as CommonElement from DemoTable group by Number;

This will produce the following output −

+--------+---------------+ | Number | CommonElement | +--------+---------------+ | 10 | 3 | | 20 | 2 | | 30 | 1 | +--------+---------------+ 3 rows in set (0.00 sec)