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

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

On Sun, Jun 10, 2007 at 06:40:19PM +0200, Andrej Kastrin wrote:

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

Starting from Andreas' code with a teensy change, it's fairly
straight-forward.

The original:

SELECT word_a || ',' || word_b as pair, count(*)
FROM
(
SELECT a.word AS word_a, b.word AS word_b
FROM
foo a
JOIN
foo b
ON (
a.id=b.id
AND a.word != b.word
AND a.word > b.word
) AS bla
GROUP BY pair;

Now with duplicates allowed. Note that I've just commented out one
line and changed > to >=.

SELECT word_a || ',' || word_b as pair, count(*)
FROM
(
SELECT a.word AS word_a, b.word AS word_b
FROM
foo a
JOIN
foo b
ON (
a.id=b.id
/* AND a.word != b.word */
AND a.word >= b.word
) AS bla
GROUP BY pair;

Hope this helps :)

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate