Count two different columns in a single query in MySQL?

You can use CASE statement to count two different columns in a single query. To understand the concept, let us first create a table. The query to create a table is as follows.

mysql> create table CountDifferentDemo
   - > (
   - > ProductId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   - > ProductName varchar(20),
   - > ProductColor varchar(20),
   - > ProductDescription varchar(20)
   - > );
Query OK, 0 rows affected (1.06 sec)

Insert some records in the table using insert command.

The query is as follows

mysql> insert into CountDifferentDemo(ProductName,ProductColor,ProductDescription) values('Product-1','Red','Used');
Query OK, 1 row affected (0.46 sec)
mysql> insert into CountDifferentDemo(ProductName,ProductColor,ProductDescription) values('Product-1','Blue','Used');
Query OK, 1 row affected (0.17 sec)
mysql> insert into CountDifferentDemo(ProductName,ProductColor,ProductDescription) values('Product-2','Green','New');
Query OK, 1 row affected (0.12 sec)
mysql> insert into CountDifferentDemo(ProductName,ProductColor,ProductDescription) values('Product-2','Blue','New');
Query OK, 1 row affected (0.14 sec)
mysql> insert into CountDifferentDemo(ProductName,ProductColor,ProductDescription) values('Product-3','Green','New');
Query OK, 1 row affected (0.21 sec)
mysql> insert into CountDifferentDemo(ProductName,ProductColor,ProductDescription) values('Product-4','Blue','Used');
Query OK, 1 row affected (0.20 sec)

Display all records from the table using select statement.

The query is as follows

mysql> select *from CountDifferentDemo;

The following is the output

+-----------+-------------+--------------+--------------------+
| ProductId | ProductName | ProductColor | ProductDescription |
+-----------+-------------+--------------+--------------------+
|         1 | Product-1   | Red          | Used               |
|         2 | Product-1   | Blue         | Used               |
|         3 | Product-2   | Green        | New                |
|         4 | Product-2   | Blue         | New                |
|         5 | Product-3   | Green        | New                |
|         6 | Product-4   | Blue         | Used               |
+-----------+-------------+--------------+--------------------+
6 rows in set (0.01 sec)

Here is the query to count two different columns in a single query i.e. we are counting the occurrence of a particular color “Red” and description “New”

mysql> select ProductName,
   - > SUM(CASE WHEN ProductColor = 'Red' THEN 1 ELSE 0 END) AS Color,
   - > SUM(CASE WHEN ProductDescription = 'New' THEN 1 ELSE 0 END) AS Desciption
   - > from CountDifferentDemo
   - > group by ProductName;

The following is the output

+-------------+-------+------------+
| ProductName | Color | Desciption |
+-------------+-------+------------+
| Product-1   |     1 |          0 |
| Product-2   |     0 |          2 |
| Product-3   |     0 |          1 |
| Product-4   |     0 |          0 |
+-------------+-------+------------+
4 rows in set (0.12 sec)

George John

Published on 19-Mar-2019 12:32:40

• Related Questions & Answers
• Update two columns with a single MySQL query
• MySQL query to combine two columns in a single column?
• How to merge queries in a single MySQL query to get the count of different values in different columns?
• Order by a function of two columns in a single MySQL query
• Comparing two columns in a single MySQL query to get one row?
• Change multiple columns in a single MySQL query?
• Get the count of two table fields in a single MySQL query?
• How to count rows from two tables in a single MySQL query?
• How to get row count of two tables in different databases in a single query?
• Implement multiple COUNT() in a single MySQL query
• MySQL query to sort multiple columns together in a single query
• Counting different distinct items in a single MySQL query?
• A single query to get the sum of count from different tables in MySQL?
• Count multiple rows and display the result in different columns (and a single row) with MySQL
• Custom sorting using two different columns in MySQL?