Consider the classic Student and Classes many-many relationship, where a student can attend multiple classes and a class contains multiple students.
CREATE TABLE students(
id serial PRIMARY KEY,
name text,
gender text NOT NULL
);
CREATE TABLE schools(
id serial PRIMARY KEY,
name text,
);
CREATE TABLE classes(
id serial PRIMARY KEY,
name text,
school_id integer NOT NULL REFERENCES schools (id)
);
CREATE TABLE students_classes(
id serial PRIMARY KEY,
class_id integer NOT NULL REFERENCES classes (id),
student_id integer NOT NULL REFERENCES students (id),
);
The overall query is much bigger - consider that there are schools and other things that add to the complexity of the problem. So I need to use window functions to get things like total_students
.
I want a query that gets me all the classes, the total number of students enrolled in that class, the number of guys enrolled and the number of girls.
class_id | n_students | n_guys | n_girls
____________________________________________
| | |
I have the following so far, can I get some help the number of guys and girls?
SELECT
school_id,
w.class_id,
w.n_students,
w.n_guys,
w.n_girls
FROM schools
JOIN classes ON classes.school_id = schools.id
JOIN (
c.id AS class_id,
COUNT(*) OVER (PARTITION BY sc.class_id) AS n_students,
{Something} AS n_guys,
{Something} AS n_girls
FROM students_classes AS sc
JOIN classes AS c ON sc.class_id = c.id
) as w ON w.class_id = classes.id
WHERE school_id = 81;
You could use this, no need to use windows/analytic function
Change male
and female
to your text value of your students.gender
column
SELECT
s.school_id,
c.class_id,
COUNT(*) AS n_students,
SUM(CASE WHEN st.gender = 'male' THEN 1 ELSE 0 END) AS n_guys,
SUM(CASE WHEN st.gender = 'female' THEN 1 ELSE 0 END) AS n_girls
FROM schools s
INNER JOIN classes c
ON c.school_id = schools.id
INNER JOIN students_classes sc
ON sc.class_id = classes.id
INNER JOIN students st
ON st.id = sc.student_id
WHERE s.school_id = 81
GROUP BY s.school_id, c.class_id
ORDER BY s.school_id, c.class_id;