[GENERAL] subquery/alias question

[Madison Kelly]

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend