Re: How to count pairs?

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
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net