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 | 02006-02-13 | 02006-02-14 | 02006-02-15 |
02006-02-16 | 682006-02-17 | 2562006-02-18 | 1042006-02-19 | 342006-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