Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.10.1/8.10.1) with ESMTP id e582lrb42065 for ; Wed, 7 Jun 2000 22:47:53 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id WAA14108; Wed, 7 Jun 2000 22:46:35 -0400 (EDT) To: Joseph Shraibman cc: Bruce Momjian , "pgsql-sql@postgresql.org" Subject: Re: counting distinct values In-reply-to: <393EF10A.7CAEAB64@selectacast.net> References: <200006080015.UAA04389@candle.pha.pa.us> <393EF10A.7CAEAB64@selectacast.net> Comments: In-reply-to Joseph Shraibman message dated "Wed, 07 Jun 2000 21:04:10 -0400" Date: Wed, 07 Jun 2000 22:46:35 -0400 Message-ID: <14104.960432395@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200006/83 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? > ... 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. regards, tom lane