Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eO6QQ-00025F-RS for pgsql-sql@arkaria.postgresql.org; Sun, 10 Dec 2017 18:34:07 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eO6QQ-0003ei-7e for pgsql-sql@arkaria.postgresql.org; Sun, 10 Dec 2017 18:34:06 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1eO6QP-0003eZ-Tf for pgsql-sql@lists.postgresql.org; Sun, 10 Dec 2017 18:34:06 +0000 Received: from mail-wr0-x233.google.com ([2a00:1450:400c:c0c::233]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1eO6QL-0005E4-Ek for pgsql-sql@lists.postgresql.org; Sun, 10 Dec 2017 18:34:04 +0000 Received: by mail-wr0-x233.google.com with SMTP id s66so15380676wrc.9 for ; Sun, 10 Dec 2017 10:34:00 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=message-id:from:to:subject:date:mime-version:content-language :thread-index; bh=nkfsYJokED65jeCRRcINybKQysPS1TigO9Lgr44CCGo=; b=pIx6rOiDXI9C3rGjKvDf4+FjphEtR7Lv4d+sYpmyWVifs7me31qtpKXoZ4jZLkzgTv /74L30NPcGSm+cUbSJzyvfk0pX/J+uHDrDci3/OoGDIanE1vsiArrlfjDSJRhASSMytQ w15Wi93v5Jvgif065cIcEA9Qim+iKm4/N96K5G4ZwaY8WwHpjpv0TxnyKl5kmxIQ2sk8 yzhkIfZRluNEiF4WgQ8iSPW14A+1iSa3A/qXfQU7hYvYEHPB9XckUoNTdXc03z7DI0+E bATzYOmT0l5DLDxI1OvbnVHgFFjTTzBfb4GMCsiQrA7VaGrQFkk4xFEB9UwFHt5B9aPJ AaCw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:message-id:from:to:subject:date:mime-version :content-language:thread-index; bh=nkfsYJokED65jeCRRcINybKQysPS1TigO9Lgr44CCGo=; b=FB/eCd5TE/tIDQYl3vBeB2x+eTLCicUgahuU3CsSOyKk7WXEtuzjufbvAylyoj6MeS 7TzrkxGjLnSylMnPSjNJ/ROp3fyGN7BAQg7HcNdkY6lo0ptn4S97TGsh1i4faBcRQTc9 PyFeoReyCVm5TOBVuaJk6cruLrQNLNjO2JX3NI5t8zqM7Cv+gw7pAgma8B/najLGjRx7 mntCt1QpVsnKmxzJhkxUSb29KFatOrNERnpqghVEktl3A6My9LiQa1e2UUqjDiwhAhwL G/foorvG/OlLtsyb9tcNsJSpM9VlNKRaAQAlXxfG1jX1c1LT6V/jJeYy4Eqspv/Y3YJe 9VoQ== X-Gm-Message-State: AJaThX43d8NlCPeveuRad2rE+69jypkrD0LVNHbqyPJe7VUCFmqqcueo puWMHQP58DX5uxqAtVT4UarXWw== X-Google-Smtp-Source: AGs4zMZNz595q9VKZwujYhJhRSEwfSO+JIxSJt6hps/Ks0Sf/1hNv3KxosngpxLAmcyAZOvZlzOs4A== X-Received: by 10.223.176.150 with SMTP id i22mr31303887wra.257.1512930838160; Sun, 10 Dec 2017 10:33:58 -0800 (PST) Received: from PADOUE (mau78-1-88-184-109-217.fbx.proxad.net. [88.184.109.217]) by smtp.gmail.com with ESMTPSA id a126sm6724258wma.11.2017.12.10.10.33.57 for (version=TLS1 cipher=ECDHE-RSA-AES128-SHA bits=128/128); Sun, 10 Dec 2017 10:33:57 -0800 (PST) Message-ID: <5a2d7e15.841a1c0a.ad030.7447@mx.google.com> X-Google-Original-Message-ID: <015601d371e5$70076a60$50163f20$@lepretre@gmail.com> From: =?iso-8859-1?Q?Olivier_Lepr=EAtre?= To: Subject: window function ? Date: Sun, 10 Dec 2017 19:33:54 +0100 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0157_01D371ED.D1CBD260" X-Mailer: Microsoft Office Outlook 12.0 Content-Language: fr Thread-Index: AdNx5VCFspLmEfu+RYOpR6Tm9cr0vQ== X-Antivirus: Avast (VPS 171210-2, 10/12/2017), Outbound message X-Antivirus-Status: Clean List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. ------=_NextPart_000_0157_01D371ED.D1CBD260 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I have a table containing sort of boxes in different categories described b= y three columns categorie/box/count In each categorie, I want to associate each box with the count of the other= s (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) =3D> 2 not added cat1 box23 6 6 (2+1+3) =3D> 6 not added cat1 box34 1 11 (2+6+3) =3D> 1 not added cat1 box37 3 9 (2+6+1) =3D> 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 =E9lectronique a =E9t=E9 v=E9rifi=E9e p= ar le logiciel antivirus Avast. https://www.avast.com/antivirus ------=_NextPart_000_0157_01D371ED.D1CBD260 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hi,

 

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

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

 <= /o:p>

As an example :

 

cat     box     count

cat1    box21= 2

cat1    b= ox23 6

cat1  &n= bsp; box34 1

cat1 &nbs= p;  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) =3D> 2 not added

cat1    box23 6    &n= bsp;   6 (2+1+3) =3D> 6 not added

cat1    box34 1    &= nbsp;   11 (2+6+3) =3D> 1 not added

cat1    box37 3    = ;    9 (2+6+1) =3D> 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) &n= bsp;    

 

cat8   = box02 10       5 (2+3)

=

cat8    box87 2    = ;    13 (10+3)

cat8    box46 3        12 (= 10+2)

 

I searched thru lateral and window functi= ons but didn't manage to do that.

 

thanks for an= y help.

 <= /span>

Olivier


3D"" Garant= i sans virus. www.avast.com
= ------=_NextPart_000_0157_01D371ED.D1CBD260--