subquery/alias question

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