Count value for multiple columns in MySQL?

MySQLMySQLi Database

To count value for multiple columns, use the CASE statement. Let us first create a table::

mysql> create table countValueMultipleColumnsDemo
   -> (
   -> Value1 int,
   -> Value2 int,
   -> Value3 int
   -> );
Query OK, 0 rows affected (0.62 sec)

Following is the query to insert some records in the table using insert command:

mysql> insert into countValueMultipleColumnsDemo values(10,15,10);
Query OK, 1 row affected (0.15 sec)

mysql> insert into countValueMultipleColumnsDemo values(20,30,10);
Query OK, 1 row affected (0.14 sec)

mysql> insert into countValueMultipleColumnsDemo values(40,10,60);
Query OK, 1 row affected (0.18 sec)

Following is the query to display records from the table using select command:

mysql> select *from countValueMultipleColumnsDemo;

This will produce the following output

+--------+--------+--------+
| Value1 | Value2 | Value3 |
+--------+--------+--------+
| 10     | 15     | 10     |
| 20     | 30     | 10     |
| 40     | 10     | 60     |
+--------+--------+--------+
3 rows in set (0.00 sec)

Following is the query to count value for multiple columns:

mysql> SELECT (SUM(CASE WHEN Value1 = 10 THEN 1 ELSE 0 END) +
   -> SUM(CASE WHEN Value2 = 10 THEN 1 ELSE 0 END) +
   -> SUM(CASE WHEN Value3 = 10 THEN 1 ELSE 0 END)) TOTAL_COUNT
   -> from countValueMultipleColumnsDemo;

This will produce the following output

+-------------+
| TOTAL_COUNT |
+-------------+
| 4           |
+-------------+
1 row in set (0.00 sec)
raja
Published on 17-Apr-2019 11:03:51
Advertisements