Re: How to count pairs?

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(at)fetter(dot)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