Thread: Can't put sub-queries values in queries results?
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. 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,
>
> 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 On 21 Jul 2000, Manuel Lemos wrote: > SELECT f1, (SELECT COUNT(*) FROM t2 WHERE t2.f2=t1.f1) AS my_count FROM t1
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 --