Received: from p1.selectacast.net (IDENT:root@p1.selectacast.net [206.41.1.251]) by hub.org (8.10.1/8.10.1) with ESMTP id e583Bvb90890 for ; Wed, 7 Jun 2000 23:11:57 -0400 (EDT) Received: from selectacast.net (IDENT:jks@d1 [209.219.91.66]) by p1.selectacast.net (8.9.3/8.9.3) with ESMTP id XAA32485; Wed, 7 Jun 2000 23:09:56 -0400 Message-ID: <393F0E82.E2F1F4A8@selectacast.net> Date: Wed, 07 Jun 2000 23:09:54 -0400 From: Joseph Shraibman Organization: Xtenit X-Mailer: Mozilla 4.72 [en] (X11; U; Linux 2.2.15 i686) X-Accept-Language: en MIME-Version: 1.0 To: Tom Lane CC: Bruce Momjian , "pgsql-sql@postgresql.org" Subject: Re: counting distinct values References: <200006080015.UAA04389@candle.pha.pa.us> <393EF10A.7CAEAB64@selectacast.net> <14104.960432395@sss.pgh.pa.us> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200006/84 Tom Lane wrote: > > Joseph Shraibman writes: > >>>> Using the example from > >>>> http://www.postgresql.org/docs/aw_pgsql_book/node59.html, what would I > >>>> do if I wanted to know the number of different cities where I had a > >>>> friend in each state? select count(city) group by state; would not work > >>>> because if you had two friends in the same city it would be counted > >>>> twice. > > Er, what's wrong with select count(distinct city) group by state? I thought I tried that, but it didn't work the first time. Guess I mistyped something. Sorry. > > > ... now suppose I want to have the number of distictive b's in the > > results as well. I try: > > > playpen=> select a, count(m.oid), avg(m.n), sum(m.n), (select > > count(mm.oid) from tablem mm where m.a = mm.a group by mm.b ) from > > tablem m group by a; > > ERROR: More than one tuple returned by a subselect used as an > > expression. > > playpen=> > > > ... even though the subselect should only return one tuple. > > Not unless there's only one b value for any one a value --- otherwise > the sub-select will return one row per b group. The error message looks > correct to me. > OK I thought that group by was eleminating duplicates which would then be counted. That evolved from q simple select(*) ... group by that worked. Don't know where I screwed it up.