subquery/alias question - Mailing list pgsql-general

Hi all,

   I've read 7.2.1.3 (as short as it is) in the PgSQL docs, but don't
see what I am doing wrong... Maybe you can help?

   I've got a query;

SELECT
    d.dom_id,
    d.dom_name,
    (SELECT COUNT(*) FROM users u WHERE u.usr_dom_id=d.dom_id)
    AS
    usr_count
FROM
    domains d
ORDER BY d.dom_name ASC;

   Where 'usr_count' returns the number of entries in 'users' that point
to a given entry in 'domains'. Pretty straight forward so far. The
trouble is:

SELECT
    d.dom_id,
    d.dom_name,
    (SELECT COUNT(*) FROM users u WHERE u.usr_dom_id=d.dom_id)
    AS
    usr_count
FROM
    domains d
WHERE
    usr_count > 0
ORDER BY d.dom_name ASC;

   Causes the error:

ERROR:  column "usr_count" does not exist

   It works if I use:

SELECT
    d.dom_id,
    d.dom_name,
    (SELECT COUNT(*) FROM users u WHERE u.usr_dom_id=d.dom_id)
    AS
    usr_count
FROM
    domains d
WHERE
    (SELECT COUNT(*) FROM users u WHERE u.usr_dom_id=d.dom_id) > 0
ORDER BY d.dom_name ASC;

   This seems terribly inefficient (and ugly), and I can't see why the
results from 'usr_count' can't be counted... I can use 'usr_count' to
sort the results...

   Thanks all!

Madi