Subject: Re: window function ?
To: pgsql-sql@lists.postgresql.org
References: <5a2d7e15.841a1c0a.ad030.7447@mx.google.com>
From: Andreas Kretschmer <andreas@a-kretschmer.de>
Message-ID: <d3a908bd-cc65-719d-7cb8-19a36cbefd5f@a-kretschmer.de>
Date: Sun, 10 Dec 2017 19:48:58 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.4.0
MIME-Version: 1.0
In-Reply-To: <5a2d7e15.841a1c0a.ad030.7447@mx.google.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk


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