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
