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