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