From: Joseph Shraibman <jks@selectacast.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Bruce Momjian <pgman@candle.pha.pa.us>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: counting distinct values
Date: Wed, 07 Jun 2000 23:09:54 -0400
Message-ID: <393F0E82.E2F1F4A8@selectacast.net> (raw)
References: <200006080015.UAA04389@candle.pha.pa.us>
<393EF10A.7CAEAB64@selectacast.net>
<14104.960432395@sss.pgh.pa.us>
Tom Lane wrote:
>
> Joseph Shraibman <jks@selectacast.net> 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. <shrug> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: jks@selectacast.net, tgl@sss.pgh.pa.us, pgman@candle.pha.pa.us
Subject: Re: counting distinct values
In-Reply-To: <393F0E82.E2F1F4A8@selectacast.net>
References: <200006080015.UAA04389@candle.pha.pa.us> <393EF10A.7CAEAB64@selectacast.net> <14104.960432395@sss.pgh.pa.us> <393F0E82.E2F1F4A8@selectacast.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox