Re: counting distinct values

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?

> ... 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