Re: How to count pairs? - Mailing list pgsql-general

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