Thread: SubSelect as a column

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

Lars <lars@sscsinc.com> writes:
> 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;

Nope, doesn't work in 6.5.*.  It is there in current sources for the
upcoming 7.0 release.  Can't think of any good workaround in 6.5...
        regards, tom lane

Tom Lane wrote:

> Lars <lars@sscsinc.com> writes:
> > 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;
>
> Nope, doesn't work in 6.5.*.  It is there in current sources for the
> upcoming 7.0 release.  Can't think of any good workaround in 6.5...
>
>                         regards, tom lane
>
> ************

i would do an UNION  of the tuples coming from the joint
with the tuples from "users" who don't have a counterpart in "ticket",
using "not exists" and adding to them a zero count.
Hoping it will help
regards
P.Jacquot

Thanks, I will give that a try. Hopefully the NOT EXISTS will not be too
slow. It is my understanding, I could be very wrong, that NOT EXISTS will
not take advantage of my indexes.

On another note, any idea when PostgreSQL 7 will be released? It sure
would be nice to have the subselect as a colunm feature.

Thanks,

-Lars

On Tue, 8 Feb 2000, Patrick JACQUOT wrote:

> Tom Lane wrote:
> 
> > Lars <lars@sscsinc.com> writes:
> > > 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;
> >
> > Nope, doesn't work in 6.5.*.  It is there in current sources for the
> > upcoming 7.0 release.  Can't think of any good workaround in 6.5...
> >
> >                         regards, tom lane
> >
> > ************
> 
> i would do an UNION  of the tuples coming from the joint
> with the tuples from "users" who don't have a counterpart in "ticket",
> using "not exists" and adding to them a zero count.
> Hoping it will help
> regards
> P.Jacquot
> 
> 
> ************
> 
>