I have two tables as follows:

**TABLE marklist:**

```
student_id  class_id    subject_1   subject_2   subject_3   subject_4   subject_5
----------- ----------- ----------- ----------- ----------- ----------- ----------
1           9           78          87                                  95
2           9           67          95                                    87
3           9           85          84                                    85
4           10          70                      65          78    
5           10          75                      80          81    
6           10          80                      75          82    

```

**Table subject\_names**

```
column_name     subject_name
--------------- -------------
subject_1       English
subject_2       Chemistry
subject_3       Economics
subject_4       Accounts
subject_5       Biology

```

Now, I need to generate a report like this for class\_id = 9

```
column_name     subject_name  no_of_students
--------------- ------------- --------------
subject_1       English       3
subject_2       Chemistry     3
subject_3       Economics     0
subject_4       Accounts      0
subject_5       Biology       3

```

In short, I have to generate a report with column\_names, subject\_name and the number of students from class\_id = 9 (or 10, whatever) who have appeared for that subject.

All that I have managed to do is

1.

```
    SELECT sn.column_name, sn.subject_name FROM subject_names sn;

```

and

2.

```
    SELECT ml.class_id,
           count(ml.subject_1) AS s1,
           count(ml.subject_2) AS s1,
           count(ml.subject_3) AS s1,
           count(ml.subject_4) AS s1,
           count(ml.subject_5) AS s1,
      FROM marklist ml
     WHERE ml.class_id = 9;

```

I don't understand how do I go ahead and pivot the results of query 2 with query one. I may be going into the wrong direction but I don't have an idea.