join - SQL count all that appear in multiple rows

Question

This is a confusing question to ask, so I think it is best to just show an example of what I need. I have a data table that looks like Table A below. It is documenting the classes that students take, so there is a column for the class subject and a column for the student's name. What I need is a table that lists every combination of 2 subjects and the number of students in each combination, like Table B. (i.e. John is the only student in both History and Spanish, John and Sam are both in History and Math etc.) I can get the subject combinations with a cross join, but I don't know how to get the number of students in each combination.

Table A :

| class     | name |
| --------- | ---- |
| Spanish   | John |
| History   | Sam  |
| Math      | Sam  |
| History   | John |
| Math      | John |
| Spanish   | Mark |

Table B:

|class1  | class2  | number of students |
|--------|---------|--------------------|
|Spanish | History | 1                  |
|History | Math    | 2                  |
|Spanish | Math    | 1                  |

Answer 1

You'd have to create a join for the same table A with two different aliases a1, a2 where they have the same key a2.name=a1.name but you have to make sure that the classes are different a2.class < a1.class. You have to use < when comparing the classes so that you avoid duplicates. Last step would be group the results by class from a1 and a2.

SELECT a1.class, a2.class, COUNT(*)
FROM
A a1
JOIN A a2 ON a2.name=a1.name AND a2.class < a1.class
GROUP BY a1.class, a2.class;