agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Andreas Joseph Krogh <andreas@visena.com>
To: pgsql-sql@postgresql.org
Subject: Problems with PARTITION BY with count() in window-func
Date: Wed, 8 Nov 2017 11:17:39 +0100 (CET)
Message-ID: <VisenaEmail.2f.7083bea2d9429cac.15f9b162e38@tc7-visena> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
Hi all.
I'm trying to count() all log-entries per activity per month in a separate
column using count() over(partition by ...) but get an error I don't understand.
Here's the schema:
CREATE TABLE log_entry( entity_id SERIAL PRIMARY KEY, start_date DATE NOT NULL
, activity_idBIGINT NOT NULL ); INSERT INTO log_entry(start_date, activity_id)
VALUES('2017-01-01', 1) , ('2017-01-02', 1) , ('2017-01-03', 2) , ('2017-01-04',
2) , ('2017-02-01', 1) , ('2017-02-01', 2) , ('2017-02-01', 1) , ('2017-02-02',
1) , ('2017-02-02', 1) , ('2017-02-03', 1); When issuing this query: select
date_trunc('month', log.start_date::TIMESTAMP WITHOUT TIME ZONE) AS month , log
.activity_id ,count(log.entity_id) AS num_logs , count(log.entity_id) OVER
total_for_month_windowAS total_for_month FROM log_entry log WHERE 1 = 1 AND
date_trunc('month', log.start_date::TIMESTAMP WITHOUT TIME ZONE) BETWEEN
'2017-01-01'::DATE AND '2017-05-01'::DATE GROUP BY month, log.activity_id, log
.entity_id WINDOW total_for_month_window AS (PARTITION BY date_trunc('month',
log.start_date::TIMESTAMP WITHOUT TIME ZONE)) ORDER BY date_trunc('month', log
.start_date::TIMESTAMP WITHOUT TIME ZONE) ASC ;
I get this:
month activity_id num_logs total_for_month 2017-01-01 00:00:00.000000 1 1 4
2017-01-01 00:00:00.000000 1 1 4 2017-01-01 00:00:00.000000 2 1 4 2017-01-01
00:00:00.000000 2 1 4 2017-02-01 00:00:00.000000 1 1 6 2017-02-01
00:00:00.000000 2 1 6 2017-02-01 00:00:00.000000 1 1 6 2017-02-01
00:00:00.000000 1 1 6 2017-02-01 00:00:00.000000 1 1 6 2017-02-01
00:00:00.000000 1 1 6
But what I really want is this result:
month activity_id num_logs total_for_month 2017-01-01 00:00:00.000000 1 2 4
2017-01-01 00:00:00.000000 2 2 4 2017-02-01 00:00:00.000000 1 5 6 2017-02-01
00:00:00.000000 2 1 6
that is, not having to group by log.entity_id, trying like this:
select date_trunc('month', log.start_date::TIMESTAMP WITHOUT TIME ZONE) AS
month , log.activity_id , count(log.entity_id) AS num_logs , count(log
.entity_id)OVER total_for_month_window AS total_for_month FROM log_entry log
WHERE1 = 1 AND date_trunc('month', log.start_date::TIMESTAMP WITHOUT TIME ZONE)
BETWEEN'2017-01-01'::DATE AND '2017-05-01'::DATE GROUP BY month, log.activity_id
WINDOWtotal_for_month_window AS (PARTITION BY date_trunc('month', log
.start_date::TIMESTAMP WITHOUT TIME ZONE)) ORDER BY date_trunc('month', log
.start_date::TIMESTAMP WITHOUT TIME ZONE) ASC ;
Note the missing log.entity_id in GROUP BY.
but this gives the error:
[42803] column "log.entity_id" must appear in the GROUP BY clause or be used
in an aggregate function
Details
Any idea how to solve this?
Thanks.
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com;
<https://www.visena.com;
view thread (3+ messages) latest in thread
Message-ID: <VisenaEmail.2f.7083bea2d9429cac.15f9b162e38@tc7-visena>
Permalink: ../VisenaEmail.2f.7083bea2d9429cac.15f9b162e38@tc7-visena/
Also on: postgresql.org/message-id/VisenaEmail.2f.7083bea2d9429cac.15f9b162e38@tc7-visena
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andreas@visena.com
Subject: Re: Problems with PARTITION BY with count() in window-func
In-Reply-To: <VisenaEmail.2f.7083bea2d9429cac.15f9b162e38@tc7-visena>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox