Geek Answers Handbook

Make SELECT COUNT of 2 tables in MySQL

I have 2 tables STUDENT AND COURSE.

STUDENT has the following columns:

SID INTERGER
NAME VARCHAR
DEPARTMENT INTEGER
REGISTRATIONDATE DATE

COURSE has the following columns:

CID INTERGER
SID INTERGER
ENROLLEDATE

I want to get the total number of STUDENTS for each registration date, by department, as well as the number of students studying at each course for this registration date, which can be determined ENROLLEDATE between REGISTRATIONDATE and REGISTRATIONDATE + 28

If my input is:

STUDENT:
1,John,CS,11/01/2014
2,Jim,CS,11/01/2014
3,Jane,LAW,10/01/2014
4,Rose,Engineering,11/01/2014

COURSE:
1,1,11/10/2014
1,2,11/11/2014
2,3,11/11/2014

OUTPUT:
StudentCount,Department,RegistrationDate,StudentInCourseCount
2,CS,11/01/2014,2
1,LAW,10/01/2014,1
1,Engineering,11/01/2014,

StudentCount = the number of students registered in the department at each registration date

Department = department name for this account

RegistrationDate = each unique registration period

StudentInCourseCount = number of students in each course, where ENROLLEDATE is between REGISTRATIONDATE and REGISTRATIONDATE + 28

: StudentInCourseCount OUTPUT , Rose .

.

, StudentInCourseCount ,

SELECT COUNT(sd.SID) As STUDENTCOUNT,sd.DEPARTMENT,sd.REGISTRATIONDATE,
(
    SELECT COUNT(p.SID) 
    FROM COURSE p
    WHERE sd.SID=p.SID AND p.ENROLLDATE BETWEEN sd.REGISTRATIONDATE AND sd.REGISTRATIONDATE+28 
 )  AS StudentInCourseCount
FROM STUDENT sd
GROUP BY DEPARTMENT,REGISTRATIONDATE
ORDER BY REGISTRATIONDATE DESC;

.

+4
2

. Conditional Aggregate, StudentInCourseCount. : Osa E , .

SELECT Count(DISTINCT s.SID) STUDENTCOUNT,
       sd.DEPARTMENT,
       sd.REGISTRATIONDATE,
       Count(CASE
               WHEN ENROLLDATE BETWEEN REGISTRATIONDATE AND REGISTRATIONDATE + 28 THEN 1
             END) StudentInCourseCount
FROM   STUDENT S
       LEFT JOIN COURSE C
         ON S.SID = C.SID
GROUP  BY DEPARTMENT,
          REGISTRATIONDATE
+1

. , , ,

SELECT COUNT(DISTINCT s.SID) StudentCount
  ,s.DEPARTMENT
  ,s.REGISTRATIONDATE
  ,COUNT(CASE
               WHEN c.ENROLLDATE BETWEEN REGISTRATIONDATE AND REGISTRATIONDATE + 28 THEN 1
             END) StudentInCourseCount
FROM STUDENT s LEFT OUTER JOIN 
  COURSE c ON s.SID = c.SID
GROUP BY s.REGISTRATIONDATE, s.DEPARTMENT
0

More articles:


All Articles