CODEWITHSUNDEEP
postgresql
wakakak
wakakak

Reputation: 842

Postgresql get the distinct count when using limit and offset

I'm just wondering if there is an easy way to get a distinct count when selecting a joined multiple rows query and using limit and offset as pagination in postgresql.

For example, I have 3 tables, student, teacher, and relational table between the student and teacher.

student table:

id name
1 Student1
2 Student2
3 Student3
4 Student4
5 Student5
6 Student6
7 Student7
8 Student8
9 Student9

... and so on up to Student100

teacher table:

id name
1 Teacher1
2 Teacher2
3 Teacher3

student_teacher table:

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

... and so on until all 100 students are with teacher1, teacher2, and teacher3

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
WHERE teacher.id = 2
LIMIT 10 OFFSET 0

the "total_count" would be 300, but I want it to be 100 instead because there are only 100 distinct student (I can see that there are only 100 rows).

Is there an easy way to achieve this? Any help would be much appreciated.

Upvotes: 0

Views: 1286

Answers (2)

Mitko Keckaroski
Mitko Keckaroski

Reputation: 1040

Maybe this is the answer that you are looking for.

SELECT DISTINCT(student.*),(select count(DISTINCT(student_id)) from student_teacher) as total_count  from student
JOIN student_teacher on student_teacher.studentId = student.id
JOIN teacher on student_teacher.teacherId = teacher.id
WHERE teacher.id = 2
LIMIT 10 OFFSET 0

Upvotes: 0

forpas
forpas

Reputation: 164099

Use COUNT() window function after you filter the table student_teacher for teacherId = 2 and then join to students.

SELECT s.*, st.total_count  
FROM student s
JOIN (
  SELECT *, COUNT(*) OVER() AS total_count 
  FROM student_teacher 
  WHERE teacherId = 2
) st ON st.studentId = s.id
LIMIT 10 OFFSET 0;

There is no need to join teachers.

Upvotes: 1

Related Questions