MySQL-এ পৃথক টেক্সটের একাধিক ঘটনা গণনা কীভাবে করবেন?

এই কাজে aggregate ফাংশন count এর সাথে if() ব্যবহার করা যায়। ধারণা বুঝতে একটি টেবিল তৈরি করি।

mysql> create table CountOccurrencesDemo 
    -> ( 
    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    -> TechnicalSubject varchar(100) 
    -> ); 
Query OK, 0 rows affected (0.68 sec)

এখন INSERT কমান্ড ব্যবহার করে কিছু রেকর্ড যোগ করি।

mysql> insert into CountOccurrencesDemo(TechnicalSubject) values('Java'); 
Query OK, 1 row affected (0.14 sec) 
mysql> insert into CountOccurrencesDemo(TechnicalSubject) values('MongoDB'); 
Query OK, 1 row affected (0.13 sec) 
mysql> insert into CountOccurrencesDemo(TechnicalSubject) values('MySQL'); 
Query OK, 1 row affected (0.16 sec) 
mysql> insert into CountOccurrencesDemo(TechnicalSubject) values('MySQL'); 
Query OK, 1 row affected (0.07 sec) 
mysql> insert into CountOccurrencesDemo(TechnicalSubject) values('MySQL'); 
Query OK, 1 row affected (0.10 sec) 
mysql> insert into CountOccurrencesDemo(TechnicalSubject) values('Java'); 
Query OK, 1 row affected (0.15 sec) 
mysql> insert into CountOccurrencesDemo(TechnicalSubject) values('Java'); 
Query OK, 1 row affected (0.10 sec) 
mysql> insert into CountOccurrencesDemo(TechnicalSubject) values('Java'); 
Query OK, 1 row affected (0.09 sec) 
mysql> insert into CountOccurrencesDemo(TechnicalSubject) values('Java'); 
Query OK, 1 row affected (0.11 sec) 
mysql> insert into CountOccurrencesDemo(TechnicalSubject) values('MongoDB'); 
Query OK, 1 row affected (0.21 sec)

SELECT স্টেটমেন্ট ব্যবহার করে সকল রেকর্ড দেখি।

mysql> select * from CountOccurrencesDemo;

আউটপুট:

+----+------------------+ 
| Id | TechnicalSubject | 
+----+------------------+ 
| 1  | Java             | 
| 2  | MongoDB          | 
| 3  | MySQL            | 
| 4  | MySQL            | 
| 5  | MySQL            | 
| 6  | Java             | 
| 7  | Java             | 
| 8  | Java             | 
| 9  | Java             | 
| 10 | MongoDB          | 
+----+------------------+ 
10 rows in set (0.00 sec)

MySQL-এ একাধিক টেক্সটের ঘটনা গণনার কোয়েরি:

mysql> select count(if(tbl.TechnicalSubject LIKE '%Java%', 1, null)) as JavaOccurrence, 
    -> count(if(tbl.TechnicalSubject LIKE '%MySQL%', 1, null)) as MySQLOccurrence, 
    -> count(if(tbl.TechnicalSubject LIKE '%MongoDB%', 1, null)) as MongoDBOccurrence 
    -> from CountOccurrencesDemo tbl;

আউটপুট:

+----------------+-----------------+-------------------+ 
| JavaOccurrence | MySQLOccurrence | MongoDBOccurrence | 
+----------------+-----------------+-------------------+ 
| 5              | 3               | 2                 | 
+----------------+-----------------+-------------------+ 
1 row in set (0.05 sec)