Sv: Not counting duplicates of declared pratition in OVER()-clause - Mailing list pgsql-sql

From Andreas Joseph Krogh
Subject Sv: Not counting duplicates of declared pratition in OVER()-clause
Date
Msg-id VisenaEmail.e2.91a23a9fffcf3cd3.16003ca4629@tc7-visena
Whole thread Raw
In response to Not counting duplicates of declared pratition in OVER()-clause  (Andreas Joseph Krogh <andreas@visena.com>)
List pgsql-sql
På tirsdag 28. november 2017 kl. 18:54:47, skrev Andreas Joseph Krogh <andreas@visena.com>:
Hi.
 
I'm trying to prevent duplicate values from being part of SUM().
 
(complete schema with INSERTs below)
 
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, which I'd then like to sum but only count values from the same month once:
 
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 (         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_trunc('month', log.start_date::TIMESTAMP WITHOUT TIME ZONE), log.activity_id) AS 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         FROM
             log_entry log
         ORDER BY date_trunc('month', log.start_date::TIMESTAMP WITHOUT TIME ZONE) ASC, log.activity_id ASC
     ) AS info
    LEFT OUTER JOIN stuff ON info.month = stuff.month
;
 
It seems I forgot to turn my brain on, sorry.
 
This query gives me what I want (using row_number() and an outer query with FILTER on rownum=1):
 
SELECT q.*    , SUM(q.new_value) FILTER (WHERE q.rownum = 1) OVER() AS total_new_value_sum
FROM (
SELECT info.*, stuff.value + info.total_for_month AS new_value    , row_number() OVER (partition by info.month) as rownum
FROM (         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_trunc('month', log.start_date::TIMESTAMP WITHOUT TIME ZONE), log.activity_id) AS 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         FROM
             log_entry log
         ORDER BY date_trunc('month', log.start_date::TIMESTAMP WITHOUT TIME ZONE) ASC, log.activity_id ASC
     ) AS info
    LEFT OUTER JOIN stuff ON info.month = stuff.month
     ) q
;
 
Gives:
monthactivity_idnum_logs_per_activitytotal_for_monthnew_valuerownumtotal_new_value_sum
2017-01-01 00:00:00.000000148301141
2017-01-01 00:00:00.000000248302141
2017-02-01 00:00:00.000000110121111141
2017-02-01 00:00:00.00000022121112141
2017-03-01 00:00:00.000000111NULL1141
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 
Attachment

pgsql-sql by date:

Previous
From: Andreas Joseph Krogh
Date:
Subject: Not counting duplicates of declared pratition in OVER()-clause
Next
From: Ertan Küçükoğlu
Date:
Subject: Timestamp alculation identical to Microsoft Excel results