Is it possible to use the result of a subquery in a WHERE?

Question

PostgreSQL

There is a request for lessons:
As a result, I receive the following request:

SELECT (SELECT Count(education.contents_progress.id) AS 
               started_content_blocks_count 
        FROM   "education"."contents_progress" 
               INNER JOIN "education"."contents_has_lessons" 
                       ON "education"."contents_has_lessons"."lesson_id" = 
                          "education"."lessons"."id" 
               INNER JOIN "education"."contents" 
                       ON "education"."contents"."id" = 
                          "education"."contents_has_lessons"."content_id" 
        WHERE  education.contents_progress.content_block_id = 
               education.contents.id 
               AND "education"."contents_progress"."user_id" = ?) AS 
       "started_content_blocks_count" 
FROM   "education"."lessons" 
WHERE  "education"."lessons"."deleted_at" IS NULL 
       AND started_content_blocks_count > 0 
LIMIT  2

I am getting an error:

SQLSTATE[42703]: Undefined column: 7 ERROR: column "started_content_blocks_count" does not exist

Tried via HAVING

SELECT (SELECT Count(education.contents_progress.id) AS 
               started_content_blocks_count 
        FROM   "education"."contents_progress" 
               INNER JOIN "education"."contents_has_lessons" 
                       ON "education"."contents_has_lessons"."lesson_id" = 
                          "education"."lessons"."id" 
               INNER JOIN "education"."contents" 
                       ON "education"."contents"."id" = 
                          "education"."contents_has_lessons"."content_id" 
        WHERE  education.contents_progress.content_block_id = 
               education.contents.id 
               AND "education"."contents_progress"."user_id" = ?) AS 
       "started_content_blocks_count" 
FROM   "education"."lessons" 
WHERE  "education"."lessons"."deleted_at" IS NULL 
HAVING started_content_blocks_count > 0 
LIMIT  2

The result is the same. What am I doing wrong?

Answer 1

It was necessary to write a subquery directly in HAVING