Can't put sub-queries values in queries results?

Manuel Lemos wrote:

Hello,

I want to look in a table and count how many rows of other table have a given
field that matches the value of the first table. I don't want to join because
if there are no matches for a given value of the first table, the query
does not return me any results for that value.

For instance I have a table t1 with field f1 and table t2 with field f2.

t1.f1
0
1
2

t2.f2
0
0
1

I want the result to be:

f1 | my_count
---+---------
0 | 2
1 | 1
2 | 0

so I do

SELECT f1, (SELECT COUNT(*) FROM t2 WHERE t2.f2=t1.f1) AS my_count FROM t1

PostgreSQL does not seem to understand this. I wonder if this is a
limitation or I am doing something wrong.

If I can't do what I want this way, I wonder if is there some other way to
do it besides making two queries by passing the values from one to the
other.

What about defining a function for the sub-query:

CREATE FUNCTION count_subs( INT4 ) RETURNS INT4 AS '
SELECT COUNT(*) FROM t2 WHERE t2.f2=$1;
' LANGUAGE 'SQL';

Then you should be able to:

SELECT f1, count_subs(f1) FROM t1;

Hope that helps,
Andrew.
--
_____________________________________________________________________
Andrew McMillan, e-mail: Andrew@cat-it.co.nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267

Hello Andrew,

On 22-Jul-00 02:42:17, you wrote:

I want to look in a table and count how many rows of other table
have a given
field that matches the value of the first table. I don't want to
join because
if there are no matches for a given value of the first table, the query
does not return me any results for that value.

For instance I have a table t1 with field f1 and table t2 with field f2.

t1.f1
0
1
2

t2.f2
0
0
1

I want the result to be:

f1 | my_count
---+---------
0 | 2
1 | 1
2 | 0

so I do

SELECT f1, (SELECT COUNT(*) FROM t2 WHERE t2.f2=t1.f1) AS my_count FROM t1

What about this:

SELECT f1, COUNT(f2) FROM t1, t2 WHERE f1=f2 GROUP BY f1

or something along those lines.

As I mentioned joins would suppress values of t1 that does not exist in t2. In this
case it would return only.

f1 | my_count
---+---------
0 | 2
1 | 1

Try this and you will see:

DROP TABLE t1;
DROP TABLE t2;
CREATE TABLE t1 (f1 INT);
INSERT INTO t1 (f1) VALUES (0);
INSERT INTO t1 (f1) VALUES (1);
INSERT INTO t1 (f1) VALUES (2);
CREATE TABLE t2 (f2 INT);
INSERT INTO t2 (f2) VALUES (0);
INSERT INTO t2 (f2) VALUES (0);
INSERT INTO t2 (f2) VALUES (1);
SELECT f1, COUNT(f2) FROM t1, t2 WHERE f1=f2 GROUP BY f1;

Regards,
Manuel Lemos

Web Programming Components using PHP Classes.
Look at: http://phpclasses.UpperDesign.com/?user=mlemos@acm.org
--
E-mail: mlemos@acm.org
URL: http://www.mlemos.e-na.net/
PGP key: http://www.mlemos.e-na.net/ManuelLemos.pgp
--

What version are you using?
Current sources allow this, and 7.0.2 should as well.

sszabo=# select * from a;
a
---
3
4

(4 rows)

sszabo=# select * from b;
a
---
3
4
3
3
(4 rows)

sszabo=# select distinct a, (select count(*) from b where b.a=a.a) from a;
a | ?column?
---+----------
3 | 3
4 | 1
| 0
(3 rows)

Stephan Szabo
sszabo@bigpanda.com

Hello Stephan,

On 22-Jul-00 15:50:21, you wrote:

What version are you using?
Current sources allow this, and 7.0.2 should as well.

I am using 6.4 . I wanted to use earlier versions but they require a
larger shared memory (1MB I suppose) than it is available on my ISP
machine.

PostgreSQL documentation says that I should ask the system administrator to
allow for more shared memory but my ISP won't do it unless I upgrade my
hosting option from a virtual server to a dedicated server. Since I can't
justify the cost only with this necessity, I am not going to upgrade.

I wonder if there isn't another way to configure PostgreSQL build to avoid
this problem that did not exist in version 6.4 .

sszabo=# select distinct a, (select count(*) from b where b.a=a.a) from a;
a | ?column?
---+----------
3 | 3
4 | 1
| 0
(3 rows)

Yes, that's what I need as long that last a column is not a NULL because
that is what I get with joins.

Regards,
Manuel Lemos

Web Programming Components using PHP Classes.
Look at: http://phpclasses.UpperDesign.com/?user=mlemos@acm.org
--
E-mail: mlemos@acm.org
URL: http://www.mlemos.e-na.net/
PGP key: http://www.mlemos.e-na.net/ManuelLemos.pgp
--

Manuel Lemos wrote:

Hello Stephan,

On 22-Jul-00 15:50:21, you wrote:

What version are you using?
Current sources allow this, and 7.0.2 should as well.

I am using 6.4 . I wanted to use earlier versions but they require a
larger shared memory (1MB I suppose) than it is available on my ISP
machine.

An ISP, allowing to run your own application programs on his
system and looking at 1MB of memory - today. I assume they do
accounting of used CPU seconds as well, don't they? Big blue
is watching you...

Just amused,

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

Manuel Lemos wrote:

SELECT f1, (SELECT COUNT(*) FROM t2 WHERE t2.f2=t1.f1) AS my_count FROM t1

PostgreSQL does not seem to understand this. I wonder if this is a
limitation or I am doing something wrong.

If I can't do what I want this way, I wonder if is there some other way to
do it besides making two queries by passing the values from one to the
other.

What about defining a function for the sub-query:

CREATE FUNCTION count_subs( INT4 ) RETURNS INT4 AS '
SELECT COUNT(*) FROM t2 WHERE t2.f2=$1;
' LANGUAGE 'SQL';

Then you should be able to:

SELECT f1, count_subs(f1) FROM t1;

That seems to work, thank you, but I can't rely on things that are specific
of PostgreSQL because I need it to work on databases with functions.

Anyway, I wonder why PostgreSQL accepts this syntax but could not accept
sub-queries as column value expression.

Any other ideas?

You can possibly do a join between t1 and t2 and UNION that with the set
of records which don't join with a '0' in the count column. A lot more
work.

Personally I don't get bogged down tying to be database agnostic - I use
PostgreSQL extensions when they're useful because I figure I can do that
with something that is BSD or GPL in ways that I wouldn't dream of tying
myself to a commercial product. Also, most of my experience with
databases is with non-SQL ones, where extensions are just the whole 4GL
/ query language :-)

Are functions not available in other SQL dialects?

Cheers,
Andrew.
--
_____________________________________________________________________
Andrew McMillan, e-mail: Andrew@cat-it.co.nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267

Hello Andrew,

On 23-Jul-00 01:29:37, you wrote:

What about defining a function for the sub-query:

CREATE FUNCTION count_subs( INT4 ) RETURNS INT4 AS '
SELECT COUNT(*) FROM t2 WHERE t2.f2=$1;
' LANGUAGE 'SQL';

Then you should be able to:

SELECT f1, count_subs(f1) FROM t1;

That seems to work, thank you, but I can't rely on things that are specific
of PostgreSQL because I need it to work on databases with functions.

Anyway, I wonder why PostgreSQL accepts this syntax but could not accept
sub-queries as column value expression.

Any other ideas?

You can possibly do a join between t1 and t2 and UNION that with the set
of records which don't join with a '0' in the count column. A lot more
work.

Yes.

Personally I don't get bogged down tying to be database agnostic - I use
PostgreSQL extensions when they're useful because I figure I can do that
with something that is BSD or GPL in ways that I wouldn't dream of tying
myself to a commercial product. Also, most of my experience with
databases is with non-SQL ones, where extensions are just the whole 4GL
/ query language :-)

That's because you are commited to a single database. I am a Web
application developer, so database application portability matters to me
because my applications market is larger if do not depend on a particular
DBMS specific features.

Are functions not available in other SQL dialects?

I don't know. I just don't want to rely on something that advanced for so
little use.

Regards,
Manuel Lemos

Web Programming Components using PHP Classes.
Look at: http://phpclasses.UpperDesign.com/?user=mlemos@acm.org
--
E-mail: mlemos@acm.org
URL: http://www.mlemos.e-na.net/
PGP key: http://www.mlemos.e-na.net/ManuelLemos.pgp
--

Hello Jan,

On 22-Jul-00 20:03:39, you wrote:

What version are you using?
Current sources allow this, and 7.0.2 should as well.

I am using 6.4 . I wanted to use earlier versions but they require a
larger shared memory (1MB I suppose) than it is available on my ISP
machine.

An ISP, allowing to run your own application programs on his
system and looking at 1MB of memory - today. I assume they do
accounting of used CPU seconds as well, don't they? Big blue
is watching you...

Yes, because that's a virtual server with about 150 users in the same
machine. Anyway the greatest problem is that they seem to need to
recompile the OS kernel or some other maintenance work that would affect
every user hosted on the same machine, so they only do that if I was on a
dedicated server.

Anyway, I don't see why I can't configure those requirements during
PostgreSQL build, especially when in past versions it worked with less
shared memory. I wonder if isn't there a way to hack PostgreSQL source to
make it work with less shared memory as in past versions.

Regards,
Manuel Lemos

Web Programming Components using PHP Classes.
Look at: http://phpclasses.UpperDesign.com/?user=mlemos@acm.org
--
E-mail: mlemos@acm.org
URL: http://www.mlemos.e-na.net/
PGP key: http://www.mlemos.e-na.net/ManuelLemos.pgp
--