Re: counting distinct values

Tom Lane wrote:
>
> Joseph Shraibman <jks(at)selectacast(dot)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.