From: | Lars <lars(at)sscsinc(dot)com> |
---|---|
To: | pgsql-sql(at)postgreSQL(dot)org |
Subject: | SubSelect as a column |
Date: | 2000-02-08 00:54:48 |
Message-ID: | Pine.BSF.4.10.10002071642050.53230-100000@maximillion.sscsinc.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-sql |
I am trying to use a subselect as a column name, but it appears as if this
is not supported in Postgresql. Here is the query:
SELECT u.idnum, u.username,
(SELECT COUNT(t.techid)
FROM ticket t
WHERE t.techid = u.idnum)
FROM users u;
the desired output would be:
idnum|username|?column?
-----+--------+--------
6|lomboy | 3
2|stuart | 6
4|trevor | 0
9|victor | 0
I can do this with the INNER JOIN:
SELECT u.idnum, MAX(u.username), COUNT(t.techid)
FROM users u, ticket t
WHERE t.techid = u.idnum
GROUP BY u.idnum;
But this will only return the those Whose count is not 0:
idnum|username|?column?
-----+--------+--------
6|lomboy | 3
2|stuart | 6
I have tried in vain to figure out how to do this correctly, but I am at a
loss. Any advice on how to get this to work.
Thank you very much in advance,
-Lars
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2000-02-08 03:33:49 | Re: [SQL] SubSelect as a column |
Previous Message | kaiq | 2000-02-07 21:34:35 | Re: [GENERAL] using ID as a key |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2000-02-08 03:33:49 | Re: [SQL] SubSelect as a column |
Previous Message | Marten Feldtmann | 2000-02-07 17:06:18 | Re: [SQL] Re: [HACKERS] Proposed Changes to PostgreSQL |