From: | "Pedro B(dot)" <pedro(dot)borracha(at)netcabo(dot)pt> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: ... more than one count with left join |
Date: | 2006-02-20 20:56:25 |
Message-ID: | 200602202056.25468.pedro.borracha@netcabo.pt |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Monday 20 February 2006 20:39, Mark R. Dingee Pedro wrote:
|> Pedro,
|>
|> Would something such as this suffice?
Hello Mark,
It's far superior to what i was doing, serialization wise. Thank you.
However, it still leaves me with the big headache of the left joins with the
"count ... where..."...
Thanks,
\\pb
|>
|> Mark
|>
|> create function get_date_range(date, date) returns setof date as '
|> DECLARE
|> cur date;
|> BEGIN
|> cur := $1;
|>
|> while cur <= $2 LOOP
|> return next cur;
|> cur := cur + interval ''1 day'';
|> end LOOP;
|> return;
|> END;' language 'plpgsql';
|>
|> dev=# select * from get_date_range('2/1/2006'::date, '2/28/2006'::date);
|> get_date_range
|> ----------------
|> 2006-02-01
|> 2006-02-02
|> 2006-02-03
|> 2006-02-04
|> 2006-02-05
|> 2006-02-06
|> 2006-02-07
|> 2006-02-08
|> 2006-02-09
|> 2006-02-10
|> 2006-02-11
|> 2006-02-12
|> 2006-02-13
|> 2006-02-14
|> 2006-02-15
|> 2006-02-16
|> 2006-02-17
|> 2006-02-18
|> 2006-02-19
|> 2006-02-20
|> 2006-02-21
|> 2006-02-22
|> 2006-02-23
|> 2006-02-24
|> 2006-02-25
|> 2006-02-26
|> 2006-02-27
|> 2006-02-28
|> (28 rows)
|>
|> On Monday 20 February 2006 15:30, Pedro B. wrote:
|> > 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) andcreate
|>
|> function get_date_range(date, date) returns setof date as '
|> DECLARE
|> cur date;
|> BEGIN
|> cur := $1;
|>
|> while cur <= $2 LOOP
|> return next cur;
|> cur := cur + interval ''1 day'';
|> end LOOP;
|> return;
|> END;' language 'plpgsql';
|>
|> dev=# select * from get_date_range('2/1/2006'::date, '2/28/2006'::date);
|> get_date_range
|> ----------------
|> 2006-02-01
|> 2006-02-02
|> 2006-02-03
|> 2006-02-04
|> 2006-02-05
|> 2006-02-06
|> 2006-02-07
|> 2006-02-08
|> 2006-02-09
|> 2006-02-10
|> 2006-02-11
|> 2006-02-12
|> 2006-02-13
|> 2006-02-14
|> 2006-02-15
|> 2006-02-16
|> 2006-02-17
|> 2006-02-18
|> 2006-02-19
|> 2006-02-20
|> 2006-02-21
|> 2006-02-22
|> 2006-02-23
|> 2006-02-24
|> 2006-02-25
|> 2006-02-26
|> 2006-02-27
|> 2006-02-28
|> (28 rows)
|>
|> > 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,
|>
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Fuhr | 2006-02-21 06:31:33 | Re: ORDER BY with LTREE |
Previous Message | Mark R. Dingee Pedro | 2006-02-20 20:39:05 | Re: Given 02-01-2006 to 02-28-2006, output all days. |