SQL: Count No of Students Enrolled in Subjects

[chrisalviola]

Iam currently coding a database system for a school here, currently this is my database in MYSQL

I want to know what SQL code to use to count how many students has enrolled in every subjects in the subjects table

the new relation would appear as

idsubjects--code---desc---units--sched---NoStud
1----------cs111--------------------------10
2----------cs201--------------------------20
3----------cs202--------------------------14

 

[chrisalviola]

I have here the SQL Query
SELECT subjects.*, count(distinct enrolled.studid) as nostud FROM subjects, enrolled where subjects.idsubjects = enrolled.subjid group by subjects.idsubjects

but the problem is if there's no students enrolled on that subject it won't appear on the relation, any solution to this?

 

[demios`]

Greetings chrisalviola,

It is in the way in which you are joining the tables. By default (inner) join only produces an output when there is a corresponding item in both tables. Whereas in this specific case, you are wanting a list of all subjects regardless if they have any students linking to them or not.

I believe for this given set-up a left-join would be better suited.

SELECT s.*, count(distinct e.studid) NoStud 
FROM subjects s
LEFT JOIN enrolled e on e.subjid = s.idSubjects
GROUP BY e.subjid ASC

Hope it helps,
-dem

 

[chrisalviola]

wow it works tnks a lot