How to display the count from distinct records in the same row with MySQL?

For this, you can use GROUP_CONCAT(), COUNT() along with GROUP BY clause. Let us first create a table −

mysql> create table DemoTable
   -> (
   -> CompanyId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> CompanyName varchar(20)
   -> );
Query OK, 0 rows affected (0.62 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable(CompanyName) values('Amazon');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable(CompanyName) values('Google');
Query OK, 1 row affected (0.09 sec)
mysql> insert into DemoTable(CompanyName) values('Google');
Query OK, 1 row affected (0.07 sec)
mysql> insert into DemoTable(CompanyName) values('Microsoft');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable(CompanyName) values('Amazon');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable(CompanyName) values('Amazon');
Query OK, 1 row affected (0.07 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output−

+-----------+-------------+
| CompanyId | CompanyName |
+-----------+-------------+
|         1 |Amazon       |
|         2 |Google       |
|         3 |Google       |
|         4 |Microsoft    |
|         5 |Amazon       |
|         6 |Amazon       |
+-----------+-------------+
6 rows in set (0.00 sec)

Here is the query to display the count from distinct records in the same row with MySQL −

mysql> select CompanyName,group_concat(CompanyId),count(CompanyId) as count from DemoTable
   -> group by CompanyName
   -> having count > 1;

This will produce the following output −

+-------------+-------------------------+-------+
| CompanyName | group_concat(CompanyId) | count |
+-------------+-------------------------+-------+
| Amazon      | 1,5,6                   |     3 |
| Google      | 2,3                     |     2 |
+-------------+-------------------------+-------+
2 rows in set (0.00 sec)