Content-Disposition: inline
From: "Pedro B." <pedro.borracha@netcabo.pt>
To: pgsql-sql@postgresql.org
Subject: Re: ... more than one count with left join
Date: Mon, 20 Feb 2006 20:56:25 +0000
User-Agent: KMail/1.9.1
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Message-Id: <200602202056.25468.pedro.borracha@netcabo.pt>

On Monday 20 February 2006 20:39, Mark R. Dingee Pedro wrote:
|> =A0Pedro,
|>
|> =A0Would 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 th=
e=20
"count ... where..."...


Thanks,
\\pb

|>
|> =A0Mark
|>
|> =A0create function get_date_range(date, date) returns setof date as '
|> =A0DECLARE
|> =A0=A0 =A0 cur date;
|> =A0BEGIN
|> =A0=A0 =A0 cur :=3D $1;
|>
|> =A0=A0 =A0 while cur <=3D $2 LOOP
|> =A0=A0 =A0 =A0 =A0 =A0return next cur;
|> =A0=A0 =A0 =A0 =A0 =A0cur :=3D cur + interval ''1 day'';
|> =A0=A0 =A0 end LOOP;
|> =A0=A0 =A0 return;
|> =A0END;' language 'plpgsql';
|>
|> =A0dev=3D# select * from get_date_range('2/1/2006'::date, '2/28/2006'::d=
ate);
|> =A0=A0get_date_range
|> =A0----------------
|> =A0=A02006-02-01
|> =A0=A02006-02-02
|> =A0=A02006-02-03
|> =A0=A02006-02-04
|> =A0=A02006-02-05
|> =A0=A02006-02-06
|> =A0=A02006-02-07
|> =A0=A02006-02-08
|> =A0=A02006-02-09
|> =A0=A02006-02-10
|> =A0=A02006-02-11
|> =A0=A02006-02-12
|> =A0=A02006-02-13
|> =A0=A02006-02-14
|> =A0=A02006-02-15
|> =A0=A02006-02-16
|> =A0=A02006-02-17
|> =A0=A02006-02-18
|> =A0=A02006-02-19
|> =A0=A02006-02-20
|> =A0=A02006-02-21
|> =A0=A02006-02-22
|> =A0=A02006-02-23
|> =A0=A02006-02-24
|> =A0=A02006-02-25
|> =A0=A02006-02-26
|> =A0=A02006-02-27
|> =A0=A02006-02-28
|> =A0(28 rows)
|>
|> =A0On Monday 20 February 2006 15:30, Pedro B. wrote:
|> =A0> Hello.
|> =A0> I'm having difficulties on my first incursion through generate_seri=
es.
|> =A0>
|> =A0> The details:
|> =A0>
|> =A0> SELECT DATE_TRUNC('month', timestamp '2006-02-01' )::DATE + s.d AS
|> =A0> date, COUNT (o."04-sms") as totalcause98
|> =A0> =A0 =A0 =A0 FROM generate_series(11,19) AS s(d)
|> =A0> =A0LEFT JOIN netopia o ON (substr(o."26-insertTime",1,10) =3D
|> =A0> (DATE_TRUNC('month', timestamp'2006-02-01' =A0)::DATE + s.d) andcre=
ate
|>
|> =A0function get_date_range(date, date) returns setof date as '
|> =A0DECLARE
|> =A0 =A0 =A0cur date;
|> =A0BEGIN
|> =A0 =A0 =A0cur :=3D $1;
|>
|> =A0 =A0 =A0while cur <=3D $2 LOOP
|> =A0 =A0 =A0 =A0 =A0 return next cur;
|> =A0 =A0 =A0 =A0 =A0 cur :=3D cur + interval ''1 day'';
|> =A0 =A0 =A0end LOOP;
|> =A0 =A0 =A0return;
|> =A0END;' language 'plpgsql';
|>
|> =A0dev=3D# select * from get_date_range('2/1/2006'::date, '2/28/2006'::d=
ate);
|> =A0 get_date_range
|> =A0----------------
|> =A0 2006-02-01
|> =A0 2006-02-02
|> =A0 2006-02-03
|> =A0 2006-02-04
|> =A0 2006-02-05
|> =A0 2006-02-06
|> =A0 2006-02-07
|> =A0 2006-02-08
|> =A0 2006-02-09
|> =A0 2006-02-10
|> =A0 2006-02-11
|> =A0 2006-02-12
|> =A0 2006-02-13
|> =A0 2006-02-14
|> =A0 2006-02-15
|> =A0 2006-02-16
|> =A0 2006-02-17
|> =A0 2006-02-18
|> =A0 2006-02-19
|> =A0 2006-02-20
|> =A0 2006-02-21
|> =A0 2006-02-22
|> =A0 2006-02-23
|> =A0 2006-02-24
|> =A0 2006-02-25
|> =A0 2006-02-26
|> =A0 2006-02-27
|> =A0 2006-02-28
|> =A0(28 rows)
|>
|> =A0> o.cause01=3D98)
|> =A0> =A0GROUP BY s.d ORDER BY 1;
|> =A0>
|> =A0>
|> =A0> This query (although quite messed up on the date parameters), does
|> =A0> exactly what i want:
|> =A0> "sum column 'cause01=3D98' for a specified date range, including 0'=
s"
|> =A0>
|> =A0> =A0 =A0 date =A0 =A0| totalcause98
|> =A0> ------------+--------------
|> =A0> =A02006-02-12 | =A0 =A0 =A0 =A0 =A0 =A00
|> =A0> =A02006-02-13 | =A0 =A0 =A0 =A0 =A0 =A00
|> =A0> =A02006-02-14 | =A0 =A0 =A0 =A0 =A0 =A00
|> =A0> =A02006-02-15 | =A0 =A0 =A0 =A0 =A0 =A00
|> =A0> =A02006-02-16 | =A0 =A0 =A0 =A0 =A0 68
|> =A0> =A02006-02-17 | =A0 =A0 =A0 =A0 =A0256
|> =A0> =A02006-02-18 | =A0 =A0 =A0 =A0 =A0104
|> =A0> =A02006-02-19 | =A0 =A0 =A0 =A0 =A0 34
|> =A0> =A02006-02-20 | =A0 =A0 =A0 =A0 =A0 20
|> =A0>
|> =A0> I'm using a left join because i really need the =3D0 sums.
|> =A0> The use of substr() is due to the fact the "26-insertTime" on the
|> =A0> 'netopia' table has a default of 'default (now())::timestamp(2) wit=
hout
|> =A0> time zone'. So, i can make generate_series work with the left join
|> =A0> using the substr. I was getting ready to optimize this query, when i
|> =A0> remembered i also have the need for another column, 'totalcause99',
|> =A0> almost the same as this query, but with 'cause01=3D99' as condition.
|> =A0>
|> =A0> The maximum i was able to do without syntax errors was:
|> =A0>
|> =A0> SELECT DATE_TRUNC('month', timestamp '2006-02-01' )::DATE + s.d AS
|> =A0> date, COUNT (o."04-sms") as totalcause98,
|> =A0> =A0 =A0 =A0 COUNT (p."04-sms") as totalcause99
|> =A0> =A0 =A0 =A0 FROM generate_series(11,19) AS s(d)
|> =A0> =A0LEFT JOIN netopia o ON (substr(o."26-insertTime",1,10) =3D
|> =A0> (DATE_TRUNC('month', timestamp'2006-02-01' =A0)::DATE + s.d) and
|> =A0> o.cause01=3D98)
|> =A0> =A0LEFT JOIN netopia p ON (substr(p."26-insertTime",1,10) =3D
|> =A0> (DATE_TRUNC('month', timestamp'2006-02-01' =A0)::DATE + s.d) and
|> =A0> p.cause01=3D99)
|> =A0> =A0GROUP BY s.d ORDER BY 1;
|> =A0>
|> =A0> Reading this one aloud, i feel the "logic" of what i'm trying to do,
|> =A0> but the values of its output are.. scary to say the least, and the =
sums
|> =A0> are exactly the same on the 2 columns, and that should never happen
|> =A0> with the data i have on the table.
|> =A0>
|> =A0> I'm starting to wonder if this is actually possible to be done on o=
ne
|> =A0> single query...
|> =A0> Ideas, anyone?
|> =A0>
|> =A0> Sorry for the long email.
|> =A0> Any and all help is deeply appreciated.
|> =A0>
|> =A0> Regards,
|>