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

I am having a table as below:

id    studentName   M开发者_JAVA技巧arks

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....

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

-- 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 AS A,
   (SELECT COUNT(1) AS totalStatus,
           IF(Marks>=50, 'PASS', 'FAIL') AS status
    FROM students
    GROUP BY IF(Marks>=50, 'PASS', 'FAIL')) AS 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)

select s.id, s.studentName, s.Marks,
       case when s.Marks >= 50 then 'Pass' else 'Fail' end as Status,
       case when s.Marks >= 50 then t.TotalPass else t.TotalFail end as TotalCount
    from @Students s,
         (select SUM(case when Marks >= 50 then 1 else 0 end) as TotalPass,
                 SUM(case when Marks < 50 then 1 else 0 end) as TotalFail
              from @Students) t

Use a "case" statement to translate #'s into "pass", "fail. Combine that with group by and a count(*). I'm not clear on what "marks" and "total status" is. which one is the grade? which one is the # of grades?

http://dev.mysql.com/doc/refman/5.0/en/case-statement.html