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 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match