How to read double signups for a class in two separate databases without subqueries? SQL

I am using MySQL for an intro to databases course. We are currently practicing using aggregations and joins to create new relevant columns. I have a class table that contains information about classes such as the class id, name, code, etc.

ID  Name                        Description     Code    MaximumStudents
1   Computer Science 310 SQL    NULL            CS-HU310    10
3   Communications              NULL            COMM113     5
4   English                     NULL            ENG101      4
5   Math                        NULL            MA030       5
6   Electrical                  NULL            ECE230      10

I also have a ClassStudent table that shows all students that are signed up for a class as well as sign up times and class codes.

//Some data from this table has been removed to avoid unnecessary clutter on the post
ID  ClassID StudentID   SignUpDate
33  3           3   2020-02-04 13:12:23
34  3           7   2020-02-04 13:12:23
38  4           2   2020-02-04 13:20:12
39  4           4   2020-02-04 13:20:12
40  4           5   2020-02-04 13:20:12
41  4           7   2020-02-04 13:20:12
42  4           8   2020-02-04 13:20:12
43  4           9   2020-02-04 13:20:12
45  5           2   2020-02-04 13:35:22
46  5           3   2020-02-04 13:35:22
47  5           4   2020-02-04 13:35:22
48  5           2   2020-02-04 13:35:42
49  5           3   2020-02-04 13:35:42
50  5           4   2020-02-04 13:35:42
51  5           2   2020-02-05 15:57:20
52  5           3   2020-02-05 15:57:20
53  5           4   2020-02-05 15:57:20

The task at hand is to display the how may students have signed up for each class twice, where applicable. If you look at the table above, you can see that there are students with IDs 2, 3, and 4, that have signed up for class number 5, 3 times. So there should be an additional column called DoubleSignUps.

Using the following query I have added a few columns to answer other parts of the question that is being asked, however I can’t figure out the last column.

SELECT Class.ID, Class.Name, Class.Code, Class.MaximumStudents,
COUNT(DISTINCT ClassStudent.StudentID) AS StudentCount,
MIN(ClassStudent.SignUpDate) AS EarliestSignUp,
MAX(ClassStudent.SignUpDate) AS LatestSignUp,
COUNT(ClassStudent.StudentID) AS DoubleSignUp
FROM Class
LEFT JOIN ClassStudent
ON Class.ID=ClassStudent.ClassID
GROUP BY ID;

Any help/explanation as to how I can do this would be greatly appreciated. All I need to do is for every ClassID, there should only be one StudentID associated, and any extra would add to a double sign up count.

EDIT:

Here is the database that I am expecting to see

ID  Name                        Description     Code    MaximumStudents DoubleSignUps
1   Computer Science 310 SQL    NULL            CS-HU310    10            0
3   Communications              NULL            COMM113     5             0
4   English                     NULL            ENG101      4             0
5   Math                        NULL            MA030       5             0
6   Electrical                  NULL            ECE230      10            3

NOTE: My query above will also display a Earliest Signup column,Latest Signup, and Student Count, however there is not enough room in the window to add them.

Answer

You need a subquery that groups by ClassID and StudentID to get duplicate signups.

SELECT StudentID, ClassID
FROM ClassStudent
GROUP BY StudentID, ClassID
HAVING COUNT(*) > 1

You can then group this by ClassID to get the number of double signups for each class:

SELECT ClassID, COUNT(*) AS DoubleSignups
FROM (
    SELECT StudentID, ClassID
    FROM ClassStudent
    GROUP BY StudentID, ClassID
    HAVING COUNT(*) > 1) AS x
GROUP BY ClassID

Then you can join this with your original query.

SELECT Class.ID, Class.Name, Class.Code, Class.MaximumStudents,
    COUNT(DISTINCT ClassStudent.StudentID) AS StudentCount,
    MIN(ClassStudent.SignUpDate) AS EarliestSignUp,
    MAX(ClassStudent.SignUpDate) AS LatestSignUp,
    IFNULL(d.DoubleSignup, 0) AS DoubleSignup
FROM Class
LEFT JOIN (
    SELECT ClassID, COUNT(*) AS DoubleSignup
    FROM (
        SELECT StudentID, ClassID
        FROM ClassStudent
        GROUP BY StudentID, ClassID
        HAVING COUNT(*) > 1) AS x
    GROUP BY ClassID
) AS d ON d.ClassID = Class.ID
LEFT JOIN ClassStudent ON Class.ID=ClassStudent.ClassID
GROUP BY Class.ID