How to pass current row value to a sub query of same table in postgresql?-CodePudding

I want to count class_id in a table in such a way, that it should return like below

student_id | class_id | count_class_id
----------- ---------- ---------------
2222       | 22       | 2
4222       | 12       | 6

The query which I am writing is ( Which surely is wrong and needs improvement is below )

SELECT student_id,
       class_id,  // I want to declare it as variable
       (SELECT Count(student_id)
        FROM   teacher
        WHERE  teacher.class_id = teacher.class_id // Current Row class_id from outer of subquery ) as count_class_id
FROM   teacher

CodePudding user response：

You need to use two different table aliases:

SELECT t1.student_id,
       t1.class_id,  
       (SELECT Count(t2.student_id)
        FROM   teacher t2
        WHERE  t1.class_id = t2.class_id) as count_class_id
FROM   teacher t1