Re: ... more than one count with left join - Mailing list 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,
|>