agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedwindow function ?
2+ messages / 2 participants
[nested] [flat]
* window function ?
@ 2017-12-10 18:33 Olivier Leprêtre <o.lepretre@gmail.com>
2017-12-10 18:48 ` Re: window function ? Andreas Kretschmer <andreas@a-kretschmer.de>
0 siblings, 1 reply; 2+ messages in thread
From: Olivier Leprêtre @ 2017-12-10 18:33 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: window function ?
2017-12-10 18:33 window function ? Olivier Leprêtre <o.lepretre@gmail.com>
@ 2017-12-10 18:48 ` Andreas Kretschmer <andreas@a-kretschmer.de>
0 siblings, 0 replies; 2+ messages in thread
From: Andreas Kretschmer @ 2017-12-10 18:48 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2017-12-10 18:48 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-12-10 18:33 window function ? Olivier Leprêtre <o.lepretre@gmail.com>
2017-12-10 18:48 ` Andreas Kretschmer <andreas@a-kretschmer.de>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox