CODEWITHSUNDEEP
mysql
Fero
Fero

Reputation: 13315

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

Upvotes: 1

Views: 14471

Answers (3)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

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)

Upvotes: 3

Joe Stefanelli
Joe Stefanelli

Reputation: 135739

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

Upvotes: 2

Jody
Jody

Reputation: 8291

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

Upvotes: 0

Related Questions