Combining window functions and conditions

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.

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;


2 Answers
2

You could use this, no need to use windows/analytic function

Change male and female to your text value of your students.gender column

male

female

students.gender

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;


Because you are just using the id, you do not need the schools table. So, this query is basically joins with conditional aggregation:

id

schools

join

select c.school_id, c.id as class_id,
count(*) AS n_students,
sum( (st.gender = 'male')::int ) AS n_guys,
sum( (st.gender = 'female')::int ) AS n_girls
from classes c join
students_classes sc
on sc.class_id = c.id join
students st
on st.id = sc.student_id
where c.school_id = 81
group by c.school_id, c.id
order by c.school_id, c.id;


Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.