How to filter data by counted count?

Question

PostgreSQL

A simple query that runs without problems in MySql in Postgres gives an error. Request:

SELECT "t".*, 
(SELECT COUNT(*) FROM "user_archive_view" WHERE user_tariff_id = t.id) AS "count_archive"
  FROM "user_tariff" "t"  HAVING "count_archive"='100';

Error:
ERROR: column "count_archive" does not exist
LINE 3: FROM "user_tariff" "t" HAVING "count_archive"='100';

How to make it work?

Answer 1

HAVING is done before SELECT so it doesn't know anything about count_archive, here's a working one

SELECT "t".*, 
(SELECT COUNT(*) FROM "user_archive_view" WHERE user_tariff_id = t.id) AS "count_archive"
  FROM "user_tariff" "t"  
HAVING (SELECT COUNT(*) FROM "user_archive_view" WHERE user_tariff_id = t.id)  = '100';