SubSelect as a column

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

Responses

Browse pgsql-general by date

  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

Browse pgsql-sql by date

  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