CODEWITHSUNDEEP
postgresql
wakakak
wakakak

Reputation: 842

When using DISTINCT the COUNT (*) OVER() does not return the correct number

I had a similar question to this, but this time I use a more complex query to describe my problem...

I'm just wondering if there is an easy way to get a distinct count when selecting a joined multiple rows query in Postgresql (I am using limit and offset for pagination, but maybe it's not too important in this case).

For example, I have these tables

student table:

id name
1 Student1
2 Student2
3 Student3

teacher table:

id name
1 Teacher1
1 Teacher2

student_teacher table:

id studentId teacherId
1 1 1
2 2 1
3 3 1
4 1 2
5 2 2
6 3 2

classroom table:

id name
1 Classroom1
2 Classroom2
3 Classroom3

teacher_classoom table:

id teacherId classroomId
1 1 1
2 1 2
3 2 3

Here is my current sql query:

SELECT DISTINCT(student.*), COUNT(*) OVER() AS "total_count" from student
JOIN student_teacher on student_teacher.studentId = student.id
JOIN teacher on student_teacher.teacherId = teacher.id
JOIN teacher_classroom on teacher_classroom.teacherId = teacher.id
JOIN classroom on classroom.id = teacher_classroom.classroomId 
LIMIT 10 OFFSET 0

The "total_count" would be 9 instead of 3 (the number of distinct student after the join query) even though the result only have 3 rows. Without the "distinct" keyword, the result has 9 rows.

Is there an easy way to achieve the total_count = 3? Any help would be much appreciated.

Upvotes: 1

Views: 586

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246918

DISTINCT is calculated after window functions, so you have to use a subquery:

SELECT *, COUNT(*) OVER() AS "total_count"
FROM (SELECT DISTINCT student.*
      from student
         JOIN student_teacher on student_teacher.studentId = student.id
         JOIN teacher on student_teacher.teacherId = teacher.id
         JOIN teacher_classroom on teacher_classroom.teacherId = teacher.id
         JOIN classroom on classroom.id = teacher_classroom.classroomId
    ) AS subq
LIMIT 10 OFFSET 0;

It would be more efficient to turn the inner joins and the DISTINCT into a series of EXISTS conditions.

Upvotes: 1

Related Questions