SubSelect as a column - Mailing list pgsql-sql

From Lars
Subject SubSelect as a column
Date
Msg-id Pine.BSF.4.10.10002071642050.53230-100000@maximillion.sscsinc.com
Whole thread Raw
Responses Re: [SQL] SubSelect as a column  (Tom Lane <tgl@sss.pgh.pa.us>)
List 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



pgsql-sql by date:

Previous
From:
Date:
Subject: Re: [SQL] Re: [HACKERS] Proposed Changes to PostgreSQL
Next
From: Marten Feldtmann
Date:
Subject: Re: [SQL] Re: [HACKERS] Proposed Changes to PostgreSQL