multi select statement inside a stored procedure mysql

Question

i got these tables in my dbpre_exer5;//mysql wampserver2.2

+-----------------------+
| Tables_in_dbpre_exer5 |
+-----------------------+
| tblcourse             |
| tblcutoff             |
| tblgrades             |
| tblstud               |
| tblsub                |
+-----------------------+

now, i just want to ask how will i be able to get these outputs considering the usage of stored procedure. the output is:

+---------+----------------+---------------+-------+
| Gender  |  With Failure  | W/out Failure | Total |
+---------+----------------+---------------+-------+
| Male    |       1        |       1       |   2   |
| Female  |       2        |       1       |   3   |
+---------+----------------+---------------+-------+

by the way here is the enter code here; **

mysql> select * from tblcourse;
+-----------+------------------------+
| course_id | course_name            |
+-----------+------------------------+
|         1 | Information Technology |
|         2 | Computer Science       |
+-----------+------------------------+
2 rows in set (0.00 sec)

mysql> select * from tblgrades;
+---------+--------+-------+
| stud_id | sub_id | grade |
+---------+--------+-------+
|       1 |      1 |    80 |
|       1 |      2 |    78 |
|       2 |      2 |    75 |
|       2 |      3 |    84 |
|       3 |      1 |    81 |
|       3 |      3 |    90 |
|       4 |      1 |    74 |
|       4 |      2 |    77 |
|       5 |      2 |    76 |
|       5 |      3 |    81 |
+---------+--------+-------+
10 rows in set (0.00 sec)

mysql> select * from tblcourse;
+-----------+------------------------+
| course_id | course_name            |
+-----------+------------------------+
|         1 | Information Technology |
|         2 | Computer Science       |
+-----------+------------------------+
2 rows in set (0.00 sec)

mysql> select * from tblcutoff;
+-----------+
| passgrade |
+-----------+
|        78 |
+-----------+
1 row in set (0.00 sec)

mysql> select * from tblgrades;
+---------+--------+-------+
| stud_id | sub_id | grade |
+---------+--------+-------+
|       1 |      1 |    80 |
|       1 |      2 |    78 |
|       2 |      2 |    75 |
|       2 |      3 |    84 |
|       3 |      1 |    81 |
|       3 |      3 |    90 |
|       4 |      1 |    74 |
|       4 |      2 |    77 |
|       5 |      2 |    76 |
|       5 |      3 |    81 |
+---------+--------+-------+
10 rows in set (0.00 sec)

mysql> select * from tblstud;
+---------+-------------------+--------+-----------+
| stud_id | stud_name         | gender | course_id |
+---------+-------------------+--------+-----------+
|       1 | Angelina Jolie    | F      |         1 |
|       2 | Jennifer Garner   | F      |         1 |
|       3 | Liam Neeson       | M      |         2 |
|       4 | Paul Walker       | M      |         2 |
|       5 | Jennifer Lawrence | F      |         2 |
+---------+-------------------+--------+-----------+
5 rows in set (0.00 sec)

mysql> select * from tblsub;
+--------+------------+
| sub_id | sub_name   |
+--------+------------+
|      1 | Math 1     |
|      2 | English 1  |
|      3 | Filipino 1 |
+--------+------------+
3 rows in set (0.00 sec)

mysql>

**

my first problem is having the results "Male" and "Female" under gender.. any help? thanks a lot.

Answer 1

UPDATED

SELECT gender, 
       SUM(failure) `With failure`,
       COUNT(*) - SUM(failure) `Without failure`,
       COUNT(*) total
  FROM
(
  SELECT s.stud_id, 
         CASE WHEN s.gender = 'M' THEN 'Male' ELSE 'Female' END gender,
         MAX(CASE WHEN g.grade <  c.passgrade THEN 1 ELSE 0 END) failure
    FROM tblgrades g JOIN tblstud s 
      ON g.stud_id = s.stud_id CROSS JOIN tblcutoff c
   GROUP BY s.stud_id
) q
 GROUP BY gender

Sample output:

| GENDER | WITH FAILURE | WITHOUT FAILURE | TOTAL |
---------------------------------------------------
| Female |            2 |               1 |     3 |
|   Male |            1 |               1 |     2 |

Here is SQLFiddle demo

Answer 2

You can use a simple case statement:

CASE WHEN 'F' THEN SELECT 'Female' ELSE SELECT 'Male'

Further reading