agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Andreas Kretschmer <andreas@a-kretschmer.de>
To: pgsql-sql@lists.postgresql.org
Subject: Re: window function ?
Date: Sun, 10 Dec 2017 19:48:58 +0100
Message-ID: <d3a908bd-cc65-719d-7cb8-19a36cbefd5f@a-kretschmer.de> (raw)
In-Reply-To: <5a2d7e15.841a1c0a.ad030.7447@mx.google.com>
References: <5a2d7e15.841a1c0a.ad030.7447@mx.google.com>
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
view thread (2+ messages)
Message-ID: <d3a908bd-cc65-719d-7cb8-19a36cbefd5f@a-kretschmer.de>
Permalink: ../d3a908bd-cc65-719d-7cb8-19a36cbefd5f@a-kretschmer.de/
Also on: postgresql.org/message-id/d3a908bd-cc65-719d-7cb8-19a36cbefd5f@a-kretschmer.de
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andreas@a-kretschmer.de, pgsql-sql@lists.postgresql.org
Subject: Re: window function ?
In-Reply-To: <d3a908bd-cc65-719d-7cb8-19a36cbefd5f@a-kretschmer.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox