CODEWITHSUNDEEP
sql
Lee
Lee

Reputation: 3969

SQL query number of students per school

I have a table of students and schools. How do I select the total students per school?

I'm sure this is a very simple query, however I'm not sure how to proceed on from this:

SELECT tblSchools.name
FROM tblStudentDetails 
INNER JOIN tblSchools 
   ON tblStudentDetails.schoolId = tblSchools.id

Upvotes: 0

Views: 1774

Answers (2)

Josh McGloghlon
Josh McGloghlon

Reputation: 56

I want to add on to the accepted answer as well. Working for a school district and continuously having to pull counts of students there are a few additional things to keep in mind. What students are you looking for?

  1. Do you want active students, inactive students, or active and inactive students.
  2. Do you want to include students that have been no showed (were going to come to your school, but ended up not coming for even a day, this is recorded in most student information systems.
  3. Is the student attending multiple schools, in which case you want to exclude them from counting in their second or third school.

I've built the script with the idea of a normalized school district database, where things are broken out by school year, and enrollment.

Often a basic script for me looks a little like this.

SELECT s.SCHOOL_NAME, COUNT(stu.STUDENT_GU) AS STUDENT_COUNT
FROM STUDENT stu
JOIN STUDENT_YEAR sy ON sy.STUDENT_ID = stu.STUDENT_ID
JOIN SCHOOL_YEAR scy ON scy.SCHOOL_YEAR_ID = sy.SCHOOL_YEAR_ID 
JOIN SCHOOL s ON s.SCHOOL_ID = scy.SCHOOL_ID
JOIN YEAR y ON y.YEAR_ID = sy.YEAR_ID 
WHERE y.SCHOOL_YEAR = 2017
AND (sy.CONCURRENT IS NULL OR sy.CONCURRENT OR != 'Not Concurrent')
AND sy.ENTER_DATE IS NOT NULL
AND sy.STATUS IS NULL
GROUP BY s.SCHOOL_NAME

Because each school year is a new year, students, and schools usually have a table for the basic data that doesn't change. But also tables that are school year specific. In my example STUDENT_YEAR and SCHOOL_YEAR is where we get into the specifics regarding which kids we are actually getting, and where they currently are. I utilize the YEAR table to identify which school year I want to look at.

The STUDENT_YEAR table is where we store the students concurrency flag, enter date, and so within that table I can use in the WHERE clause a way to filter out Inactive Students, Concurrent Students, and ensure each student is counted only once.

If those year values aren't included, at least in my database, I would get all students ever enrolled for every year we've got stored.

Upvotes: 1

juergen d
juergen d

Reputation: 204746

Group by the school and use count() to count the students

SELECT s.name, count(d.id) as students_count
FROM tblSchools s
INNER JOIN tblStudentDetails d ON d.schoolId = s.id
GROUP BY s.name

Upvotes: 2

Related Questions