Am 10.12.2017 um 19:33 schrieb Olivier Leprêtre:
>
> 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.
>
>
>
>
>
test=*# select * from boxes ;
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
(12 Zeilen)
test=*# select cat, box, sum(count) over (partition by cat) - count from
boxes;
cat | box | ?column?
------+-------+----------
cat1 | box21 | 10
cat1 | box23 | 6
cat1 | box34 | 11
cat1 | box37 | 9
cat3 | box45 | 28
cat3 | box62 | 38
cat3 | box89 | 33
cat3 | box12 | 31
cat3 | box28 | 30
cat8 | box02 | 5
cat8 | box87 | 13
cat8 | box46 | 12
(12 Zeilen)
test=*#
helps that?
Regards, Andreas
--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com
