MySQL query to select a count on two separate conditions?
Use CASE statement for this. Let us first create a table −
mysql> create table DemoTable
-> (
-> StudentMarks int,
-> isValid tinyint(1)
-> );
Query OK, 0 rows affected (0.68 sec)
Insert some records in the table using insert command −
mysql> insert into DemoTable values(45,0);
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable values(78,1);
Query OK, 1 row affected (0.26 sec)
mysql> insert into DemoTable values(45,1);
Query OK, 1 row affected (0.31 sec)
mysql> insert into DemoTable values(78,1);
Query OK, 1 row affected (0.22 sec)
mysql> insert into DemoTable values(45,0);
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable values(82,1);
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable values(62,1);
Query OK, 1 row affected (0.14 sec)
Display all records from the table using select statement −
mysql> select *from DemoTable;
Output
+--------------+---------+
| StudentMarks | isValid |
+--------------+---------+
| 45 | 0 |
| 78 | 1 |
| 45 | 1 |
| 78 | 1 |
| 45 | 0 |
| 82 | 1 |
| 62 | 1 |
+--------------+---------+
7 rows in set (0.00 sec)
Here is the query to select a count on two separate conditions −
mysql> select StudentMarks,
-> sum(case
-> when StudentMarks=45
-> then case when isValid = 1 then 1 else 0 end
-> else 1 end
-> ) AS Freq
-> from DemoTable
-> group by StudentMarks;
Output
+--------------+------+
| StudentMarks | Freq |
+--------------+------+
| 45 | 1 |
| 78 | 2 |
| 82 | 1 |
| 62 | 1 |
+--------------+------+
4 rows in set (0.00 sec)
Published on 10-Jul-2019 14:54:39
