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)

AmitDiwan

Updated on 12-Nov-2019 06:28:38

• Related Questions & Answers
• Find average of corresponding records (Product Price) from duplicate product ids in MYSQL
• MySQL query to return the count of only NO values from corresponding column value
• Set different IDs for records with conditions using a single MySQL query
• How to select different values from same column and display them in different columns with MySQL?
• Add records from corresponding duplicate values in another column with MySQL
• Get minimum value from a column (floating values) with corresponding duplicate ids in MySQL
• Select Statement to retrieve the same first names with similar last name (but different case) using MySQL?
• MySQL query to concatenate records with similar corresponding ids in a single row separated by a special character
• MySQL IF() to display custom YES or NO messages
• SUM corresponding duplicate records in MySQL
• Display highest amount from corresponding duplicate ids in MySQL
• Get all the records with two different values in another column with MySQL
• How to select sum or 0 if no records exist in MySQL?
• Select minimum row value from a column with corresponding duplicate column values in MySQL
• How to merge two data frames of different length having same values in all columns but at different positions?