Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eJk6M-00041I-T3 for pgsql-sql@arkaria.postgresql.org; Tue, 28 Nov 2017 17:55:23 +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 1eJk6M-0002u7-Ez for pgsql-sql@arkaria.postgresql.org; Tue, 28 Nov 2017 17:55:22 +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 1eJk6M-0002tK-4E for pgsql-sql@lists.postgresql.org; Tue, 28 Nov 2017 17:55:22 +0000 Received: from post.visena.com ([46.226.10.50]) by magus.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1eJk6H-0007UJ-9e for pgsql-sql@lists.postgresql.org; Tue, 28 Nov 2017 17:55:21 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=visena.com; s=20141101.wh; h=Content-Type:MIME-Version:Subject:Message-ID:To:From:Date; bh=u9/MWCGQO3yY3zArzpbQNJcPBRkBw7R3RnAAr0dW500=; b=dEdjKz9ZrsLRgoT+Fsxmb4c/abTyLG9AwRaEqlTCHyFQzQ5RMOaKScHHw3NcjtbTZFAhMoWi0QcV6B8eNyE4z0CIWu0C871bZWcCYmwx/ThCwvrU8xvyz2aY0zmBktbIzW4HxXWuOXGn/GDwK4gHFHysQMLjifJQ+InFo9bGOUE=; Received: from [10.0.1.10] (helo=tc7-visena.wh.internal.visena.com) by post.visena.com with esmtp (Exim 4.82) (envelope-from ) id 1eJk6E-0007X4-1d for pgsql-sql@lists.postgresql.org; Tue, 28 Nov 2017 18:55:16 +0100 Received: from localhost ([127.0.0.1] helo=tc7-visena.wh.internal.visena.com) by tc7-visena.wh.internal.visena.com with esmtp (Exim 4.86_2) (envelope-from ) id 1eJk5n-0005Iw-Cw for pgsql-sql@lists.postgresql.org; Tue, 28 Nov 2017 18:54:47 +0100 Date: Tue, 28 Nov 2017 18:54:47 +0100 (CET) From: Andreas Joseph Krogh To: pgsql-sql@lists.postgresql.org Message-ID: Subject: Not counting duplicates of declared pratition in OVER()-clause MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_Part_995_1889134508.1511891687098" X-Mailer: Visena Mail 2.1.0-SNAPSHOT X-Spam-Score: -1.0 X-Spam-Report: SpamAssasin (score=-1.0, required 5.0 ALL_TRUSTED=-1,HTML_MESSAGE=0.001) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_995_1889134508.1511891687098 Content-Type: multipart/related; boundary="----=_Part_996_1456500641.1511891687098" ------=_Part_996_1456500641.1511891687098 Content-Type: multipart/alternative; boundary="----=_Part_997_45642785.1511891687116" ------=_Part_997_45642785.1511891687116 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi. =C2=A0 I'm trying to prevent duplicate values from being part of SUM(). =C2=A0 (complete schema with INSERTs below) =C2=A0 I have this query to count all log-entries per activity per month in a=20 sub-query, then adding a value from another table in the outer query, which= I'd=20 then like to sum but only count values from the same month once: =C2=A0 SELECT info.*, stuff.value + info.total_for_month AS new_value , SUM (stuff.value +info.total_for_month) OVER () AS total_new_value_sum FROM (= =20 SELECT DISTINCT date_trunc('month', log.start_date::TIMESTAMP WITHOUT TIME = ZONE) AS month , log.activity_id , count(log.entity_id) OVER(partition by date_tr= unc( 'month', log.start_date::TIMESTAMP WITHOUT TIME ZONE), log.activity_id) AS= =20 num_logs_per_activity ,count(log.entity_id) OVER (partition by date_trunc( 'month', log.start_date::TIMESTAMP WITHOUT TIME ZONE)) AS total_for_month F= ROM=20 log_entrylog ORDER BY date_trunc('month', log.start_date::TIMESTAMP WITHOU= T=20 TIME ZONE) ASC, log.activity_id ASC ) AS info LEFT OUTER JOIN stuff ON inf= o .month =3D stuff.month ;=20 =C2=A0 What I want is this result: =C2=A0 month activity_id num_logs_per_activity total_for_month new_value=20 total_new_value_sum 2017-01-01 00:00:00.000000 1 4 8 30 141 2017-01-01=20 00:00:00.000000 2 4 8 30 141 2017-02-01 00:00:00.000000 1 10 12 111 141=20 2017-02-01 00:00:00.000000 2 2 12 111 141 2017-03-01 00:00:00.000000 1 1 1 = NULL=20 141=20 =C2=A0 But what I get is: =C2=A0 month activity_id num_logs_per_activity total_for_month new_value=20 total_new_value_sum 2017-01-01 00:00:00.000000 1 4 8 30 282 2017-01-01=20 00:00:00.000000 2 4 8 30 282 2017-02-01 00:00:00.000000 1 10 12 111 282=20 2017-02-01 00:00:00.000000 2 2 12 111 282 2017-03-01 00:00:00.000000 1 1 1 = NULL=20 282=20 =C2=A0 =C2=A0 The problem is I don't know how to prevent every values in "new_value"-colu= mn=20 from being included in the SUM(). =C2=A0 I'd like something like=C2=A0this: =C2=A0 , SUM(stuff.value + info.total_for_month) AS=20 total_new_value_sum =C2=A0 Any hints on how to accomplish this? =C2=A0 Here is the complete schema: DROP TABLE IF EXISTS stuff; DROP TABLE IF EXISTS log_entry; CREATE TABLE=20 log_entry( entity_idSERIAL PRIMARY KEY, start_date DATE NOT NULL, activity_= id=20 BIGINT NOT NULL, logged_for BIGINT NOT NULL ); CREATE TABLE stuff( entity_i= d=20 SERIAL PRIMARY KEY, month DATE NOT NULL UNIQUE, value INTEGER NOT NULL );= =20 INSERT INTOlog_entry(start_date, activity_id, logged_for) VALUES ('2017-01-= 01',=20 1, 5) , ('2017-01-02', 1, 5) , ('2017-01-03', 2, 5) , ('2017-01-04', 2, 5) = , ( '2017-02-01', 1, 5) , ('2017-02-01', 2, 5) , ('2017-02-01', 1, 5) , ( '2017-02-02', 1, 5) , ('2017-02-02', 1, 5) , ('2017-02-03', 1, 5) , ( '2017-01-01', 1, 6) , ('2017-01-02', 1, 6) , ('2017-01-03', 2, 6) , ( '2017-01-04', 2, 6) , ('2017-02-01', 1, 6) , ('2017-02-01', 2, 6) , ( '2017-02-01', 1, 6) , ('2017-02-02', 1, 6) , ('2017-02-02', 1, 6) , ( '2017-02-03', 1, 6) , ('2017-03-01', 1, 6); INSERT INTO stuff(month, value)= =20 VALUES('2017-01-01', 22),('2017-02-01', 99);=20 =C2=A0 Thanks in advance. =C2=A0 -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andreas@visena.com www.visena.com ------=_Part_997_45642785.1511891687116 Content-Type: text/html;charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi.
=C2=A0
I'm trying to prevent duplicate values from being part of SUM().
=C2=A0
(complete schema with INSERTs below)
=C2=A0
I have this query to count all log-entries per activity per month in a= sub-query, then adding a value from another table in the outer query, whic= h I'd then like to sum but only count values from the same month once:
=C2=A0
SELECT info.*, stuff.value + AS new_value
    , SUM(stuff.value + info.total_for_mon=
th) OVER (=
) AS total_new_value_sum
FROM (
         SELECT D=
ISTINCT
          =
     date_trunc('month', log.start_date::TIMESTAMP WITHOUT TIM=
E ZONE) AS=
 month
          =
   , log.activity_id
             , c=
ount(log.entity_id) =
OVER(parti=
tion by date_trunc('month', log.start_date::<=
span style=3D"color: rgb(0, 0, 128); font-weight: bold;">TIMESTAMP WITHOUT =
TIME ZONE), AS num_logs_per_activity
             , c=
ount(log.entity_id) =
OVER (part=
ition by date_trunc('month', log.start_date::=
TIMESTAMP WITHOUT=
 TIME ZONE)) AS total_for_month
         FROM
          =
   log_entry log
         O=
RDER BY date_trunc('month', log.start_date::<=
span style=3D"color: rgb(0, 0, 128); font-weight: bold;">TIMESTAMP WITHOUT =
TIME ZONE) ASC, log<=
/span>.activity_id      ) AS info
    LEFT O=
UTER JOIN stuff ON info.month =3D stuff.month
;
=C2=A0
What I want is this result:
=C2=A0
=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09
monthactivity_idnum_logs_per_activitytotal_for_monthnew_valuetotal_new_value_sum<= /span>
2017-01-01 00:00:00.00000014830141
2017-01-01 00:00:00.00000024830141
2017-02-01 00:00:00.00000011012111141
2017-02-01 00:00:00.0000002212111141
2017-03-01 00:00:00.000000111NULL141
=C2=A0
But what I get is:
=C2=A0
=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09
monthactivity_idnum_logs_per_activitytotal_for_monthnew_valuetotal_new_value_sum<= /span>
2017-01-01 00:00:00.00000014830282
2017-01-01 00:00:00.00000024830282
2017-02-01 00:00:00.00000011012111282
2017-02-01 00:00:00.0000002212111282
2017-03-01 00:00:00.000000111NULL282
=C2=A0
=C2=A0
The problem is I don't know how to prevent every values in "new_v= alue"-column from being included in the SUM().
=C2=A0
I'd like something like=C2=A0this:
=C2=A0
    , SUM(stuff.value + info.total_for_mon=
th) <distinct by month> AS =
total_new_value_sum
=C2=A0
Any hints on how to accomplish this?
=C2=A0
Here is the complete schema:
DROP TABLE IF EXISTS stuff;
DROP TABLE IF EXISTS log_entry;
CREATE TABLE log_ent=
ry(
    entity_id SERIAL PRIMAR=
Y KEY,
    start_date DATE NOT NUL=
L,
    activity_id BIGINT NOT =
NULL,
    logged_for BIGINT NOT N=
ULL
);

CREATE TABLE stuff(
    entity_id SERIAL PRIMAR=
Y KEY,
    month DATE NOT NULL UNIQUE,
    value INTEGER NOT NULL
);

INSERT INTO log_entr=
y(start_date, activity_id, logged_for)
VALUES ('2017-01-01', 1, 5)
    , ('2017-01-02',=
 1, 5<=
/span>)
    , ('2017-01-03',=
 2, 5<=
/span>)
    , ('2017-01-04',=
 2, 5<=
/span>)
    , ('2017-02-01',=
 1, 5<=
/span>)
    , ('2017-02-01',=
 2, 5<=
/span>)
    , ('2017-02-01',=
 1, 5<=
/span>)
    , ('2017-02-02',=
 1, 5<=
/span>)
    , ('2017-02-02',=
 1, 5<=
/span>)
    , ('2017-02-03',=
 1, 5<=
/span>)
    , ('2017-01-01',=
 1, 6<=
/span>)
    , ('2017-01-02',=
 1, 6<=
/span>)
    , ('2017-01-03',=
 2, 6<=
/span>)
    , ('2017-01-04',=
 2, 6<=
/span>)
    , ('2017-02-01',=
 1, 6<=
/span>)
    , ('2017-02-01',=
 2, 6<=
/span>)
    , ('2017-02-01',=
 1, 6<=
/span>)
    , ('2017-02-02',=
 1, 6<=
/span>)
    , ('2017-02-02',=
 1, 6<=
/span>)
    , ('2017-02-03',=
 1, 6<=
/span>)
    , ('2017-03-01',=
 1, 6<=
/span>);

INSERT INTO stuff(month, value) VALUES('2017-01-01', 22),('2017-02-01', 99);
=C2=A0
Thanks in advance.
=C2=A0
--
Andrea= s Joseph Krogh
CTO / Partner<= /span> - Visena AS
Mobile: +47 90= 9 56 963
=3D""
------=_Part_997_45642785.1511891687116-- ------=_Part_996_1456500641.1511891687098 Content-Type: image/png Content-Transfer-Encoding: base64 Content-Disposition: inline Content-ID: iVBORw0KGgoAAAANSUhEUgAAAIUAAAAYCAYAAADUIj6hAAAABHNCSVQICAgIfAhkiAAABzBJREFU aEPtmNFxHDcMhmVP3i1VECpvnjzkVIHWFfhcgVcVRKrAUgWRK/C6Al8H3lTgy0PGbzFdQc4VJP/H ADs43q6kROeJNbOYgQACIAgCWJKng4MZ5gxUGXh0U0b+SIuF9G+EWXj2Q15vbrE/NPtk9uub7Gfd t5mBx1NhqSEo8HshjbEUtlO2yIM9tsx5dZP9rPt2MzDZFAqZE4LGcFjdsg3saQaHt7fY70X949On aS+OZidDBkavD33157L4xaw2os+Mp/DAC10l2XhOCeStj0W5ajrJL8WfCi80Xgf9vVlrhg9yRONe /f7xI2vNsIcM7JwU9o6oGyJrLT8JOA1aX9sKP4wl94bAniukEbo/n7YPmuTETzIab4Y9ZWCrKexd 8M58lxPCvvD6aihfvexbEQoPYO8NQRO0JnddGO6FJYaVsBde7cXj7KRk4LsqDxQzCYeGsJNgaXZe +JXkyGgWINq3Gp+bHNILz+wEYs5qH1eJrouNrpDXtk4O6x1IvtD40GWyJYYdkF0jIbYZlN16xygI gl9smbMDsmFdfAKb23xGB8H/mv3tOJfArs1kukk79DEPdQ5u0g1vCvvqKXIW8mZYW+F3Tg4r8HvZ kQCCLydK8EFMQCe5N8RgL9mRG0SqQFuNvdE6beSs0qPDBuCdg0+gvClso8SbTO4ki7mQzQqBrcMH QPwReg2wW8umEe/+L8T/LEzBGF9nXjzZ46s+ITHvhegW8LL39xm6App7KYL/GE+vcYnFbJiP/0aY hdiCnRC7jaj7eiK2ETInC5PRF6LYkSN0+HabF75WuT6syCyI0YkVGGMvUC33AmfZTDUEj8u6IVhu EhRUJ2U2g6UlugyNb03Hl9obHwlxJbcRzcYjO4QPjVfGFTQav4vrmp7cpMp2qbHnBxWJbisbho2Q XI6C1sIHDUFhH4Hij4UbIev66eANeiwbkA+LBsO36zAHzoXU7AhbqLAXEiOYTXcSdO8VS9L4wN8U BIYhBd6oSUgYMijOkecRuTdQa/YiBXhbXFcnCnI2SrfeBG9NydrLYMhGHa5qB9pQIxlzAE6Okjzx JO5afGe6kmhBicWKQNKuTZ5EW+MjQY8v/9rQ0bhJSJyNGUe/JH1t8h1iMbdSPAvxHYin6YmN9YBX QmTYZZNh14vHhhhal5vtcIrJjmvszPRJdExH3MWHvykoBEc9CoCGWJisOLOGoCORs1FvIMbYA8z3 kwM59oemy6LlWrLxFLmWgiQAfEGd8S+NssbK+EhyGLxUkhhiy717waBqHOJYSEacwBejkFNhjJNj v/gANOdQxPfciP/JVBCK2cOIcg3RRJ+CPrLPNVhhN6F38VKMF3XLlIJrjU5C8gMFxvKDvOcPc4rV NjCn7KM0BV+161V8viSCuJZ8SITGJGEh7IUUlxOFMYUH1kJOCN4Wh+I5pqCuK01k40kSNtnKiKIl UfxAgW5sU5JlS05rtt5YFJF1SWoqHv6BRgQcA4/bdb9WRjmMk3jyUMAbIoyJq9e4cVmgzKt9j5gN J/aYDhk+hhjExwaPcz5PObA5Zd9bvz5UzCTZuZDidu5AchpiKSwPR+TV1bCWqBQ9nCjJ5neivC82 Nr4LeS2j1gw5LUqwBuhGQQU5UwHeSslXkwIynz2U2A2yKDgG7OffwLA3TpGRpk0Tzpj3ZEJXi/GR a6GN0e0NtppChePdcAz1FTS+FN8Kpxoiykk+J8fC5tenjbu9kSqpHLu9jBrhUuhNwVGbpyZrzrl0 HPVD8SXj5EOOD/eDC64VjvYBZLuUbIVAfBN1t/C/SU+cAGtdGo+fVnzycUX5wmn6eCKPmWYJG2E/ ppTsuRCbvUD9fwquksG5GqLVKhzDQ3GrEyLKSXhsiK3T5j9EyxffCFOYi2wUlPyFFDQAhehESDjQ GoX0ho0oj8RPou6TxHJdcT0NTcWkO0AnG7+uXsnHqcas/72wvWF+mSf7N/Watp9kTXoluzeS0fB9 9CcZ/hvhSZTfh99pispZOXL9KrGrARkNUMu9ITbScZWs7xOYNt9pwxSZtYDsX/GE3xTkrXgwAsXm fud08FiTeC+m2/o7ppo+PTS/NBK5ARpDG5YHr++DpmVfreYdiX8mnp+DzPEG9WbqJON0JBenZofs sxBA1gj5NXGvfJu/Qh7HwQh/VDUEyUxCit5hH94QfKkEdu+GwK8BX0hvCF+D67xhjmXQCXMwJCZ+ olK0A1EKRCE4snsh42w8Mv/Zhxw9iD7Cjo7CyQC/KyF6oBfShL4PYgG+CHsYKyZxvxZSZBAgjhIz YDz+AbfD37GtbaoSKzgGt+lKfI/GZtay6vG4VXTpPsh+IcQhOk9I7WYeP5AM3HZ9+DaSGIp9HIuu huC4pCGGx+YD2fcc5tfIAA0h/Et4/jX8zz4fWAasIf4UbR9Y6HO4d8jAnd4U0Y8ageuCB+c+H5R3 CHU2mTMwZ+B/y8DfSMBLLOYXVuEAAAAASUVORK5CYII= ------=_Part_996_1456500641.1511891687098-- ------=_Part_995_1889134508.1511891687098--