From: Pedro B. <pedro.borracha@netcabo.pt>
To: pgsql-sql@postgresql.org
Subject: Re: Given 02-01-2006 to 02-28-2006, output all days.
Date: Mon, 20 Feb 2006 20:30:02 +0000
Message-ID: <200602202030.02701.pedro.borracha@netcabo.pt> (raw)
In-Reply-To: <144D12D7DD4EC04F99241498BB4EEDCC220D19@nelson.osl.com>
References: <144D12D7DD4EC04F99241498BB4EEDCC220D19@nelson.osl.com>
Hello.
I'm having difficulties on my first incursion through generate_series.
The details:
SELECT DATE_TRUNC('month', timestamp '2006-02-01' )::DATE + s.d AS date,
COUNT (o."04-sms") as totalcause98
FROM generate_series(11,19) AS s(d)
LEFT JOIN netopia o ON (substr(o."26-insertTime",1,10) = (DATE_TRUNC('month',
timestamp'2006-02-01' )::DATE + s.d) and o.cause01=98)
GROUP BY s.d ORDER BY 1;
This query (although quite messed up on the date parameters), does exactly
what i want:
"sum column 'cause01=98' for a specified date range, including 0's"
date | totalcause98
------------+--------------
2006-02-12 | 0
2006-02-13 | 0
2006-02-14 | 0
2006-02-15 | 0
2006-02-16 | 68
2006-02-17 | 256
2006-02-18 | 104
2006-02-19 | 34
2006-02-20 | 20
I'm using a left join because i really need the =0 sums.
The use of substr() is due to the fact the "26-insertTime" on the 'netopia'
table has a default of 'default (now())::timestamp(2) without time zone'.
So, i can make generate_series work with the left join using the substr.
I was getting ready to optimize this query, when i remembered i also have the
need for another column, 'totalcause99', almost the same as this query, but
with 'cause01=99' as condition.
The maximum i was able to do without syntax errors was:
SELECT DATE_TRUNC('month', timestamp '2006-02-01' )::DATE + s.d AS date,
COUNT (o."04-sms") as totalcause98,
COUNT (p."04-sms") as totalcause99
FROM generate_series(11,19) AS s(d)
LEFT JOIN netopia o ON (substr(o."26-insertTime",1,10) = (DATE_TRUNC('month',
timestamp'2006-02-01' )::DATE + s.d) and o.cause01=98)
LEFT JOIN netopia p ON (substr(p."26-insertTime",1,10) = (DATE_TRUNC('month',
timestamp'2006-02-01' )::DATE + s.d) and p.cause01=99)
GROUP BY s.d ORDER BY 1;
Reading this one aloud, i feel the "logic" of what i'm trying to do, but the
values of its output are.. scary to say the least, and the sums are exactly
the same on the 2 columns, and that should never happen with the data i have
on the table.
I'm starting to wonder if this is actually possible to be done on one single
query...
Ideas, anyone?
Sorry for the long email.
Any and all help is deeply appreciated.
Regards,
--
\\pb
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: pedro.borracha@netcabo.pt
Subject: Re: Given 02-01-2006 to 02-28-2006, output all days.
In-Reply-To: <200602202030.02701.pedro.borracha@netcabo.pt>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox