window function ? - Mailing list pgsql-sql

Hi,

I have a table containing sort of boxes in different categories described by three columns categorie/box/count

In each categorie, I want to associate each box with the count of the others (sum of counts of this categorie but not the current one).

As an example :

cat     box     count

cat1    box21 2

cat1    box23 6

cat1    box34 1

cat1    box37 3

cat3    box45 12

cat3    box62 2

cat3    box89 7

cat3    box12 9

cat3    box28 10

cat8    box02 10

cat8    box87 2

cat8    box46 3

will return

cat1    box21 2        10 (6+1+3) => 2 not added

cat1    box23 6        6 (2+1+3) => 6 not added

cat1    box34 1        11 (2+6+3) => 1 not added

cat1    box37 3        9 (2+6+1) => 3 not added

cat3    box45 12       28 (2+7+9+10)

cat3    box62 2        38 (12+7+9+10)

cat3    box89 7        33 (12+2+9+10)

cat3    box12 9        31 (12+2+7+10)

cat3    box28 10       30 (12+2+7+9)      

cat8    box02 10       5 (2+3)

cat8    box87 2        13 (10+3)

cat8    box46 3        12 (10+2)

I searched thru lateral and window functions but didn't manage to do that.

thanks for any help.

Olivier