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

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
;

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
;

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com