subquery/alias question

From: Madison Kelly <linux(at)alteeve(dot)com>
To: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: subquery/alias question
Date: 2007-09-25 21:59:16
Message-ID: 46F984B4.3060408@alteeve.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2007-09-25 22:00:28 Re: Can't connect (2 dbs) or login (2 others)
Previous Message Rodrigo De Le� 2007-09-25 21:36:02 Re: pg_restore - invalid file problem