MySQL-এ ডুপ্লিকেট রেকর্ড গণনা করা

MySQL-এ IF() ফাংশন এবং GROUP BY ব্যবহার করে কোনো কলামে ডুপ্লিকেট রেকর্ড আছে কিনা এবং কতবার আবৃত্তি হয়েছে তা জানা যায়।

সিনট্যাক্স

SELECT yourColumnName, COUNT(*) AS anyVariableName, IF(COUNT(*) > 1, 'Duplicate Records', 'Not Duplicate records') AS anyVariableName FROM yourTableName GROUP BY yourColumnName;

উদাহরণ

টেবিল তৈরি করি:

mysql> create table DuplicateRecords 
    -> ( 
    -> Id int NOT NULL AUTO_INCREMENT, 
    -> Name varchar(30), 
    -> PRIMARY KEY(Id) 
    -> ); Query OK, 0 rows affected (0.82 sec)

রেকর্ড যোগ করি (অনেক ডুপ্লিকেট সহ):

mysql> insert into DuplicateRecords(Name) values('Carol'); 
mysql> insert into DuplicateRecords(Name) values('John'); 
mysql> insert into DuplicateRecords(Name) values('Sam'); 
mysql> insert into DuplicateRecords(Name) values('John'); 
mysql> insert into DuplicateRecords(Name) values('Sam'); 
mysql> insert into DuplicateRecords(Name) values('Sam'); 
mysql> insert into DuplicateRecords(Name) values('John'); 
mysql> insert into DuplicateRecords(Name) values('Carol'); 
mysql> insert into DuplicateRecords(Name) values('Carol'); 
mysql> insert into DuplicateRecords(Name) values('Mike');

সকল রেকর্ড

mysql> select *from DuplicateRecords;

আউটপুট

+----+-------+
| Id | Name  |
+----+-------+
|  1 | Carol |
|  2 | John  |
|  3 | Sam   |
|  4 | John  |
|  5 | Sam   |
|  6 | Sam   |
|  7 | John  |
|  8 | Carol |
|  9 | Carol |
| 10 | Mike  |
+----+-------+
10 rows in set (0.00 sec)

ডুপ্লিকেট গণনা করুন

mysql> SELECT Name, COUNT(*) AS Repetition, IF(COUNT(*) > 1, 'Duplicate Records', 'Not Duplicate records') AS IsDuplicateRecordsOrNot FROM DuplicateRecords GROUP BY Name;

ফলাফল

+-------+------------+-------------------------+
| Name  | Repetition | IsDuplicateRecordsOrNot |
+-------+------------+-------------------------+
| Carol | 3          | Duplicate Records       |
| John  | 3          | Duplicate Records       |
| Sam   | 3          | Duplicate Records       |
| Mike  | 1          | Not Duplicate records   |
+-------+------------+-------------------------+
4 rows in set (0.00 sec)