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)