How to count the number of columns having specific value in MySQL?

Following is the syntax −

select
sum(yourColumnName1+yourColumnName2+yourColumnName3...N) as `anyAliasName1`,
sum(yourColumnName1 and yourColumnName2 and yourColumnName3….N) as anyAliasName
from yourTableName;

Let us create a table −

mysql> create table demo36
−> (
−> id int not null auto_increment primary key,
−> value1 int,
−> value2 int,
−> value3 int
−> );
Query OK, 0 rows affected (1.68 sec)

Insert some records into the table with the help of insert command −

mysql> insert into demo36(value1,value2,value3) values(1,0,0);
Query OK, 1 row affected (0.14 sec)

mysql> insert into demo36(value1,value2,value3) values(1,0,1);
Query OK, 1 row affected (0.11 sec)

mysql> insert into demo36(value1,value2,value3) values(1,1,1);
Query OK, 1 row affected (0.22 sec)

mysql> insert into demo36(value1,value2,value3) values(0,1,0);
Query OK, 1 row affected (0.12 sec)

mysql> insert into demo36(value1,value2,value3) values(1,1,0);
Query OK, 1 row affected (0.10 sec)

Display records from the table using select statement −

mysql> select *from demo36;

This will produce the following output −

+----+--------+--------+--------+
| id | value1 | value2 | value3 |
+----+--------+--------+--------+
| 1  | 1      | 0      | 0      |
| 2  | 1      | 0      | 1      |
| 3  | 1      | 1      | 1      |
| 4  | 0      | 1      | 0      |
| 5  | 1      | 1      | 0      | 
+----+--------+--------+--------+
5 rows in set (0.00 sec)

Following is the query to count the number of columns having specific value in MySQL −

mysql> select
−> sum(value1+value2+value3) as `Total_Number_Of_1`,
−> sum(value1 and value2 and value3) as `Total_Number_Of_Rows_With_1`
−> from demo36;

This will produce the following output −

+-------------------+-----------------------------+
| Total_Number_Of_1 | Total_Number_Of_Rows_With_1 |
+-------------------+-----------------------------+
|                 9 |                           1 |
+-------------------+-----------------------------+
1 row in set (0.00 sec)

AmitDiwan

Published on 19-Nov-2020 12:40:32

• Related Questions & Answers
• How to retrieve a value with MySQL count() having maximum upvote value?
• Count number of subsets having a particular XOR value in C++
• How to count the number of occurrences of a specific value in a column with a single MySQL query?
• Count value for multiple columns in MySQL?
• How to get the count of a specific value in a column with MySQL?
• How to count number of specific symbols in a row in MySQL?
• How to combine matrices having same number of columns in R?
• Count the number of columns in a MySQL table with Java
• Count values greater and less than a specific number and display count in separate MySQL columns?
• Count number of entries in an object having specific values in multiple keys JavaScript
• Get the count of a specific value in MongoDB
• Get the count of a specific value in MongoDB quickly
• How to find the number of columns in a MySQL table?
• How to count number of columns in a table with jQuery
• Count the number of masked elements along specific axis