MySQL-এ একই ID-তে ভিন্ন রেকর্ড থাকলে Yes/No গুননা করা

এজন্য SUM() সহ CASE স্টেটমেন্ট ব্যবহার করা হয়। প্রথমে একটি টেবিল তৈরি করি:

mysql> create table DemoTable1430 (
    -> EmployeeId int,
    -> isMarried ENUM('YES','NO')
    -> );
Query OK, 0 rows affected (0.60 sec)

রেকর্ড ইনসার্ট করি:

mysql> insert into DemoTable1430 values(1001,'Yes');
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable1430 values(1001,'No');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable1430 values(1001,'Yes');
Query OK, 1 row affected (0.09 sec)
mysql> insert into DemoTable1430 values(1001,'Yes');
Query OK, 1 row affected (0.16 sec)

সব রেকর্ড দেখি:

mysql> select * from DemoTable1430;

আউটপুট:

+------------+-----------+
| EmployeeId | isMarried |
+------------+-----------+
|       1001 | YES       |
|       1001 | NO        |
|       1001 | YES       |
|       1001 | YES       |
+------------+-----------+
4 rows in set (0.00 sec)

Yes এবং No গুননের কোয়েরি:

mysql> select EmployeeId,sum(isMarried='Yes') as NumberOfMarried,
    -> sum(isMarried='No') as NumberOfUnMarried
    -> from DemoTable1430
    -> group by EmployeeId;

আউটপুট:

+------------+-----------------+-------------------+
| EmployeeId | NumberOfMarried | NumberOfUnMarried |
+------------+-----------------+-------------------+
|       1001 |               3 |                 1 |
+------------+-----------------+-------------------+
1 row in set (0.00 sec)