SubSelect as a column

Поиск
Список
Период
Сортировка
От Lars
Тема SubSelect as a column
Дата
Msg-id Pine.BSF.4.10.10002071642050.53230-100000@maximillion.sscsinc.com
обсуждение исходный текст
Ответы Re: [SQL] SubSelect as a column
Список 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 по дате отправления:

Предыдущее
От:
Дата:
Сообщение: Re: [SQL] Re: [HACKERS] Proposed Changes to PostgreSQL
Следующее
От: Marten Feldtmann
Дата:
Сообщение: Re: [SQL] Re: [HACKERS] Proposed Changes to PostgreSQL