pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSubSelect as a column
5+ messages / 4 participants
[nested] [flat]
* SubSelect as a column
@ 2000-02-08 00:54 Lars <lars@sscsinc.com>
0 siblings, 1 reply; 5+ messages in thread
From: Lars @ 2000-02-08 00:54 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] SubSelect as a column
@ 2000-02-08 03:33 Tom Lane <tgl@sss.pgh.pa.us>
parent: Lars <lars@sscsinc.com>
0 siblings, 2 replies; 5+ messages in thread
From: Tom Lane @ 2000-02-08 03:33 UTC (permalink / raw)
To: Lars <lars@sscsinc.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] SubSelect as a column
@ 2000-02-08 07:30 Nikolay Mijaylov <nmmm@nmmm.nu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: Nikolay Mijaylov @ 2000-02-08 07:30 UTC (permalink / raw)
To: pgsql-general <pgsql-general@postgreSQL.org>; Tom Lane <tgl@sss.pgh.pa.us>
Define this as function:
SELECT COUNT(t.techid)
FROM ticket t
WHERE t.techid = $1
Then use this....
SELECT u.idnum, u.username, func(u.idnum)
FROM users u;
--------------------------------------------------------------
The reboots are for hardware upgrades!
"http://www.nmmm.nu; <nmmm@nmmm.nu>
----- Original Message -----
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Lars <lars@sscsinc.com>
Cc: <pgsql-sql@postgreSQL.org>
Sent: вторник, Февруари 08, 2000 05:33
Subject: Re: [SQL] SubSelect as a column
> 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
>
> ************
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] SubSelect as a column
@ 2000-02-08 09:19 Patrick JACQUOT <patrick.jacquot@anpe.fr>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 5+ messages in thread
From: Patrick JACQUOT @ 2000-02-08 09:19 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] SubSelect as a column
@ 2000-02-08 19:08 Lars <lars@sscsinc.com>
parent: Patrick JACQUOT <patrick.jacquot@anpe.fr>
0 siblings, 0 replies; 5+ messages in thread
From: Lars @ 2000-02-08 19:08 UTC (permalink / raw)
To: Patrick JACQUOT <patrick.jacquot@anpe.fr>; +Cc: pgsql-sql
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
>
>
> ************
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2000-02-08 19:08 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-02-08 00:54 SubSelect as a column Lars <lars@sscsinc.com>
2000-02-08 03:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-02-08 07:30 ` Nikolay Mijaylov <nmmm@nmmm.nu>
2000-02-08 09:19 ` Patrick JACQUOT <patrick.jacquot@anpe.fr>
2000-02-08 19:08 ` Lars <lars@sscsinc.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox