Fero Fero - 1 month ago 10
MySQL Question

Need mysql query to get students PASS, FAIL and COUNT in single query

I am having a table as below:

id studentName Marks

1 X 60

2 Y 25

3 Z 50


Here the pass Marks is 50 and above

My output should be

id studentName Marks status totalCount

1 X 60 PASS 2

2 Y 25 FAIL 1

3 Z 50 PASS 2


Here the total count of pass is 2 as well as total number of fail is 1.

How can this be done using MYSQL QUERY.

thanks in advance....

Answer
SELECT A.id,A.studentName,A.Marks,B.status,B.totalStatus
FROM students A,
    (SELECT COUNT(1) totalStatus,IF(Marks>=50,'PASS','FAIL') status 
       FROM students GROUP BY IF(Marks>=50,'PASS','FAIL')) B
WHERE B.status = IF(A.Marks>=50,'PASS','FAIL');

This really works !!! I actually tried it !!!

lwdba@localhost (DB test2) :: CREATE TABLE students
-> (
-> id INT NOT NULL,
-> studentName VARCHAR(10),
-> Marks INT NOT NULL,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.17 sec)

lwdba@localhost (DB test2) :: INSERT INTO students VALUES
-> (1,'X',60),
-> (2,'Y',25),
-> (3,'X',50);
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0

lwdba@localhost (DB test2) :: SELECT A.id,A.studentName,A.Marks,B.status,B.totalStatus
-> FROM students A,(SELECT COUNT(1) totalStatus,IF(Marks>=50,'PASS','FAIL') status
FROM students GROUP BY IF(Marks>=50,'PASS','FAIL')) B
-> WHERE B.status = IF(A.Marks>=50,'PASS','FAIL');
+----+-------------+-------+--------+-------------+
| id | studentName | Marks | status | totalStatus |
+----+-------------+-------+--------+-------------+
| 1 | X | 60 | PASS | 2 |
| 2 | Y | 25 | FAIL | 1 |
| 3 | X | 50 | PASS | 2 |
+----+-------------+-------+--------+-------------+
3 rows in set (0.00 sec)

Give it a Try !!!

Comments