Re: How to count pairs?

A. Kretschmer wrote:
> am  Sun, dem 10.06.2007, um 13:41:27 +0200 mailte Andrej Kastrin folgendes:
>
>> Dear all,
>>
>> I need to count all pairs of instances which occure under given ID
>> number, e.g.:
>>
>> ID word
>> -------------
>> 1   car
>> 1   fruit
>> 2   car
>> 2   fruit
>> 2   vegetable
>> 3   car
>> 3   vegetable
>>
>> And the output should be simillar to:
>>
>> car fruit 2
>> car vegetable 2
>> fruit vegetable 1
>>
>>
>
> test=*# select * from foo;
>  id |   word
> ----+-----------
>   1 | car
>   1 | fruit
>   2 | car
>   2 | fruit
>   3 | car
>   3 | vegetable
>   2 | vegetable
> (7 rows)
>
> test=*# select word_a || ',' || word_b as pair,
>     count(1)
> from ( select a.word as word_a, b.word as word_b from foo a join foo b on a.id=b.id where a.word != b.word and a.word
>b.word) as bla  
> group by pair;
>       pair       | count
> -----------------+-------
>  vegetable,car   |     2
>  vegetable,fruit |     1
>  fruit,car       |     2
> (3 rows)
>
>
> Andreas
>
Thanks Andreas.

Is there a simple way to modify the proposed querry to count also the
equal pairs under ID number; e.g; I add new entry (ID=1; value=car), so:

select * from foo;
 id |   word
----+-----------
  1 | car
  1 | car
  1 | fruit
  2 | car
  2 | fruit
  2 | vegetable
  3 | car
  3 | vegetable
(8 rows)

The result according Andreas querry is as follows:
      pair       | count
-----------------+-------
 vegetable,car   |     2
 vegetable,fruit |     1
 fruit,car       |     3
(3 rows)

How to evaluate pair car,car under ID=1 too?

With my best regards, Andrej