Received: from mercury.Sun.COM (mercury.Sun.COM [192.9.25.1]) by hub.org (8.9.2/8.9.1) with SMTP id RAA39169 for ; Thu, 4 Feb 1999 17:12:41 -0500 (EST) (envelope-from dimitri@france.Sun.COM) Received: from France.Sun.COM ([129.157.188.1]) by mercury.Sun.COM (SMI-8.6/mail.byaddr) with SMTP id OAA15060; Thu, 4 Feb 1999 14:10:54 -0800 Received: from sybille.France.Sun.COM by France.Sun.COM (SMI-8.6/SMI-SVR4-sd.fkk200) id XAA28661 for ; Thu, 4 Feb 1999 23:10:53 +0100 Received: from dimitri (pc_dimitri [129.157.186.244]) by sybille.France.Sun.COM (8.8.8+Sun/8.8.8) with SMTP id XAA28444; Thu, 4 Feb 1999 23:10:52 +0100 (MET) Sender: dim@sybille.France.Sun.COM Message-ID: <36BA1ABD.31E18EFC@france.sun.com> Date: Thu, 04 Feb 1999 22:10:05 +0000 From: Dimitri Organization: Sun Microsystems X-Mailer: Mozilla 3.04Gold (X11; I; Linux 2.0.36 i586) MIME-Version: 1.0 To: Jan Wieck CC: pat@patoche.org, pgsql-sql@postgreSQL.org Subject: Re: [SQL] problem with join & count References: Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hm... Why do be complex where you can be sample? Your query is: select name, count(*) as how_often from table1, table2 where table1.number = table2.number group by name; and that's all, folks! P.S. Regarding your example, I can say that you are French! Isnt it? :)) "toto, titi" - very unique as names :)) Jan Wieck wrote: > > > i need an sql query that will return: > > > > name | how_often > > ------------------- > > toto | 2 > > titi | 1 > > tutu | 2 > > > > that is, the result table should contain all names present in the first table > > and then the number of times the number associated with the name appears in > > the second table. > > Postgres does not (and v6.5 will not) support outer joins or > subselects in the targetlist, what's required to do that in a > single SQL statement. > > What you could do is using a SQL function that covers the > count() like this: > > create function num_refs (int4) returns int4 as ' > select count(*) from tab2 where id = $1; > ' language 'SQL'; > > select item_name, num_refs(id) as how_often from tab1; > > I changed 'number' into id and name into item_name because > both are reserved words. > > Jan > > -- > > #======================================================================# > # It's easier to get forgiveness for being wrong than for being right. # > # Let's break this rule - forgive me. # > #======================================== jwieck@debis.com (Jan Wieck) # -- ===================================================== Dimitri KRAVTCHUK (dim) Sun Microsystems Benchmark Engineer France dimitri@France.Sun.COM =====================================================