Select count of values (Yes, No) with same ids but different corresponding records in MySQL?

For this, you can use SUM() along with CASE statement. Let us first create a −

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

Insert some records in the table using insert −

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)

Display all records from the table using select −

mysql> select * from DemoTable1430;

This will produce the following output −

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

Here is the query to select count of value (Yes, No) −

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

This will produce the following output −

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