window function ?

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



---
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel antivirus Avast.
https://www.avast.com/antivirus