Postgresql get the distinct count when using limit and offset

Question

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.

Note that distinct is not a function. It always applies to all columns in the select list. Enclosing one (or more) of the columns with parentheses won't change anything and is useless. distinct (a),b is the same as distinct a,(b) or distinct a,b and distinct (t.*), x is the same as distinct t.*, x — user330315
– user330315, Commented Aug 30, 2021 at 6:14

forpas · Accepted Answer · 2021-08-30 06:19:07Z

1

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.

answered Aug 30, 2021 at 6:19

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

wakakak Over a year ago

ah, yes, I was simplifying the query, the original query has more than just one relationship table, so I needed the join to teacher table.. but I am currently thinking how to incorporate the above query to my reusable function..

wakakak Over a year ago

actually, when I run the inner JOIN (select *, count( * ) over() as total_count), I still get the 300 as total count, so maybe this will not work..

forpas Over a year ago

@GlenK WHERE teacherId = 2 is applied first and then COUNT(*) OVER() calculates the number of students. So, if you get 300 then there are 300 students for teacherId = 2. Is there a case that you have duplicate students for a teacher?

wakakak Over a year ago

I will have a look at it again tomorrow.. as I said, I am simplifying the query, so maybe there is something wrong with the my query.. will let you know how it goes.. thanks

wakakak Over a year ago

I found the issue.. in the actual query, I used a LEFT JOIN to another table, and in the where clause I also have "IS NOT NULL".. maybe I should fix the query or post another question because your answer is correct given the simple query..

Mitko Keckaroski · Accepted Answer · 2021-08-30 09:49:22Z

0

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

edited Aug 30, 2021 at 9:49

answered Aug 30, 2021 at 6:45

Mitko Keckaroski

1,0891 gold badge10 silver badges16 bronze badges

2 Comments

wakakak Over a year ago

the (select count(*) from student) as total_count will give the number of student = 100, even if I delete one of the student relationship with the teacher2

Mitko Keckaroski Over a year ago

@GlenK I edited my answer. Now count is on the "student_teacher" table.

Collectives™ on Stack Overflow

Postgresql get the distinct count when using limit and offset

2 Answers 2

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related