How to read double signups for a class in two separate databases without subqueries? SQL2020-02-15 mysql sql database
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.
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.
You need a subquery that groups by
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
- How to select the nth row in a SQL database table?
- Select max value in subquery
- SQL Server : get all data from one table but condition on 2nd tables column
- Stuck! How do I join three SQL tables to answer these two questions? Or do I not need to?
- Multiple condition with two table data in Mysql
- Create inversed datatable from object list
- Insert multiple (fk) data in one of the two phpMyAdmin table
- Using CASE and COUNT together in SQL Statement
- Display the highest result of each group with relevance data?
- Easy SQL Group-By question. (I'm new)