MySQL-এ একটি কোয়েরিতে বুলিয়ান ফিল্ডের TRUE/FALSE কাউন্ট

CASE ও SUM দিয়ে বুলিয়ান কাউন্ট

MySQL-এ TINYINT(1) বুলিয়ান হিসেবে ব্যবহার হয় (১=TRUE, ০=FALSE)। একই কোয়েরিতে TRUE এবং FALSE কাউন্ট করতে SUM(condition) ব্যবহার করুন।

সিনট্যাক্স

SELECT 
  SUM(isPassed = 1) AS `True`,
  SUM(isPassed = 0) AS `False`,
  CASE WHEN SUM(isPassed = 1) > 0 THEN SUM(isPassed = 0) / SUM(isPassed = 1) END AS TotalPercentage
FROM yourTable;

উদাহরণ

টেবিল তৈরি

mysql> create table countBooleanFieldDemo (
    -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> StudentFirstName varchar(20),
    -> isPassed tinyint(1)
    -> );
Query OK, 0 rows affected (0.63 sec)

ডেটা ইনসার্ট

mysql> insert into countBooleanFieldDemo(StudentFirstName,isPassed) values('Larry',0);
Query OK, 1 row affected (0.12 sec)
mysql> insert into countBooleanFieldDemo(StudentFirstName,isPassed) values('Mike',1);
Query OK, 1 row affected (0.17 sec)
mysql> insert into countBooleanFieldDemo(StudentFirstName,isPassed) values('Sam',0);
Query OK, 1 row affected (0.21 sec)
mysql> insert into countBooleanFieldDemo(StudentFirstName,isPassed) values('Carol',1);
Query OK, 1 row affected (0.15 sec)
mysql> insert into countBooleanFieldDemo(StudentFirstName,isPassed) values('Bob',1);
Query OK, 1 row affected (0.16 sec)
mysql> insert into countBooleanFieldDemo(StudentFirstName,isPassed) values('David',1);
Query OK, 1 row affected (0.13 sec)
mysql> insert into countBooleanFieldDemo(StudentFirstName,isPassed) values('Ramit',0);
Query OK, 1 row affected (0.28 sec)
mysql> insert into countBooleanFieldDemo(StudentFirstName,isPassed) values('Chris',1);
Query OK, 1 row affected (0.20 sec)
mysql> insert into countBooleanFieldDemo(StudentFirstName,isPassed) values('Robert',1);
Query OK, 1 row affected (0.16 sec)

সকল রেকর্ড

mysql> SELECT * FROM countBooleanFieldDemo;

আউটপুট

+-----------+------------------+----------+
| StudentId | StudentFirstName | isPassed |
+-----------+------------------+----------+
|         1 | Larry            |        0 |
|         2 | Mike             |        1 |
|         3 | Sam              |        0 |
|         4 | Carol            |        1 |
|         5 | Bob              |        1 |
|         6 | David            |        1 |
|         7 | Ramit            |        0 |
|         8 | Chris            |        1 |
|         9 | Robert           |        1 |
+-----------+------------------+----------+

বুলিয়ান কাউন্ট কোয়েরি

mysql> SELECT 
    -> SUM(isPassed = 1) AS `True`,
    -> SUM(isPassed = 0) AS `False`,
    -> CASE WHEN SUM(isPassed = 1) > 0 THEN SUM(isPassed = 0) / SUM(isPassed = 1) END AS TotalPercentage
    -> FROM countBooleanFieldDemo;

আউটপুট

+------+-------+-----------------+
| True | False | TotalPercentage |
+------+-------+-----------------+
|    6 |     3 |          0.5000 |
+------+-------+-----------------+

ব্যাখ্যা: TRUE=৬, FALSE=৩, অনুপাত ৩/৬=০.৫। MySQL-এ বুলিয়ান এক্সপ্রেশন TRUE=1, FALSE=0 রিটার্ন করে, তাই SUM() সরাসরি কাজ করে।