From: | Andreas Joseph Krogh <andreas(at)visena(dot)com> |
---|---|
To: | pgsql-sql(at)lists(dot)postgresql(dot)org |
Subject: | Not counting duplicates of declared pratition in OVER()-clause |
Date: | 2017-11-28 17:54:47 |
Message-ID: | VisenaEmail.e1.e756ac5c250cdc14.16003bb2e74@tc7-visena |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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_entrylog 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 ;
What I want is this result:
month activity_id num_logs_per_activity total_for_month new_value
total_new_value_sum 2017-01-01 00:00:00.000000 1 4 8 30 141 2017-01-01
00:00:00.000000 2 4 8 30 141 2017-02-01 00:00:00.000000 1 10 12 111 141
2017-02-01 00:00:00.000000 2 2 12 111 141 2017-03-01 00:00:00.000000 1 1 1 NULL
141
But what I get is:
month activity_id num_logs_per_activity total_for_month new_value
total_new_value_sum 2017-01-01 00:00:00.000000 1 4 8 30 282 2017-01-01
00:00:00.000000 2 4 8 30 282 2017-02-01 00:00:00.000000 1 10 12 111 282
2017-02-01 00:00:00.000000 2 2 12 111 282 2017-03-01 00:00:00.000000 1 1 1 NULL
282
The problem is I don't know how to prevent every values in "new_value"-column
from being included in the SUM().
I'd like something like this:
, SUM(stuff.value + info.total_for_month) <distinct by month> AS
total_new_value_sum
Any hints on how to accomplish this?
Here is the complete schema:
DROP TABLE IF EXISTS stuff; DROP TABLE IF EXISTS log_entry; CREATE TABLE
log_entry( entity_idSERIAL PRIMARY KEY, start_date DATE NOT NULL, activity_id
BIGINT NOT NULL, logged_for BIGINT NOT NULL ); CREATE TABLE stuff( entity_id
SERIAL PRIMARY KEY, month DATE NOT NULL UNIQUE, value INTEGER NOT NULL );
INSERT INTOlog_entry(start_date, activity_id, logged_for) VALUES ('2017-01-01',
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)
VALUES('2017-01-01', 22),('2017-02-01', 99);
Thanks in advance.
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas(at)visena(dot)com <mailto:andreas(at)visena(dot)com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Joseph Krogh | 2017-11-28 18:03:29 | Sv: Not counting duplicates of declared pratition in OVER()-clause |
Previous Message | Laurenz Albe | 2017-11-26 15:18:53 | Re: Roles and security |