| 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: | Whole Thread | Raw Message | 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 |