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.

@Luv i wasn't able to make a function inside the stored procedure ..i only did it thru select statement but still the output is wrong — user2628953, Jul 29 '13 at 05:09
Duplicate: http://stackoverflow.com/questions/4706100/mysql-equivalent-of-decode-function-in-oracle-and-intersystem-cache-database — rags, Jul 29 '13 at 05:13

peterm · Accepted Answer · 2013-07-29T06:14:40.967

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

edited Jul 29 '13 at 06:14

answered Jul 29 '13 at 05:23

peterm

91,357
15
148
157

yes sir ..but the count of failure should be unique ..student_id taking subj_id ..which means the failure count must also be integrated with the subject ..stud_id&&subj_id – user2628953 Jul 29 '13 at 05:29
@user2628953 It's already "integrated" this way in `tblgrades` table, is it not? If not please elaborate explaining on your sample data what should and what shouldn't be counted as failure and we can fix the query. – peterm Jul 29 '13 at 05:35
say stud_id 4 has failed sub_id 1 and 2, the query should only count this failure as 1 .. the counting isn't dependent on the how many subjects a stud_id has failed but on if a stud_id has failed then count – user2628953 Jul 29 '13 at 05:42

score 0 · Answer 2 · answered Jul 29 '13 at 05:09

0

You can use a simple case statement:

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

multi select statement inside a stored procedure mysql

2 Answers2