SQL Count unique rows where a column contains two different values

Question

I have taken a good look around and not been able to find any questions that match mine. Maybe I am not using the right language when searching or whatever, but here goes.

I have an SQL table called Classes that looks something like this

Student_Name | Class  
--------------------  
Edgar        | Chemistry  
Allan        | Chemistry  
Burt         | Chemistry  
Edgar        | Math  
Sue          | Math  
Hamilton     | Math  
Edgar        | English  
Sue          | English  
Edgar        | German  
Ben          | German  

I want to count how many students are taking both Math and German.

Assuming the following in this example: - Student names are unique - One student can have many classes

Logically I would use a select statement to get a result set of students who are taking Math. Then I would go through each Student_Name from the result set and check them against the table to see how many are taking German.

In this case I would expect a return of 1 as only Edgar is taking both Math and German.

Here are some of the queries I have tried so far to no avail :-(

This one was after doing some research on DISTINCT:

SELECT COUNT(DISTINCT Student_Name) FROM Classes WHERE Class = 'Math' AND Class = 'German';

And this one was after finding out more about GROUP BY:

SELECT COUNT(*) FROM (
    SELECT DISTINCT Student_Name FROM Classes 
    WHERE Class IN ( 'Math', 'German' ) 
    GROUP BY Student_Name
);

Neither of these came out quite right any help would be highly appreciated.

Answer 1

SELECT  COUNT(*) totalStudent
FROM
        (
            SELECT  student_name
            FROM    Classes
            WHERE   class IN ('Math','German')
            GROUP   BY student_name
            HAVING  COUNT(*) = 2
        ) subAlias

• SQLFiddle Demo

OUTPUT

╔══════════════╗
║ TOTALSTUDENT ║
╠══════════════╣
║            1 ║
╚══════════════╝

Answer 2

Could also do the following:

select count(distinct a.Student_name)
from Classes a inner join Classes b on
     a.Class = 'German' and
     b.Class = 'Math'   and
     a.Student_Name = b.Student_name;

This solves the problem where the table contains duplicate rows (as pointed out by a commenter to another answer)