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

 

[LudusRex]

Hi there,

To count the number of students enrolled in each subject in your database, you can use the SQL COUNT function. The syntax for this function is as follows:

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

In your case, the column you would like to count is the "NoStud" column. So your code would look something like this:

SELECT COUNT(NoStud)
FROM subjects
WHERE code = 'cs111';

This will give you the total number of students enrolled in the subject with the code 'cs111'. You can repeat this for each subject code to get the count for all subjects.

Alternatively, if you want to count the total number of students enrolled in all subjects, you can use the SUM function. The syntax for this function is as follows:

SELECT SUM(column_name)
FROM table_name
WHERE condition;

In your case, the column you would use is still "NoStud". So your code would look like this:

SELECT SUM(NoStud)
FROM subjects;

This will give you the total number of students enrolled in all subjects.

I hope this helps. Happy coding!