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