Re: ... more than one count with left join
От | Pedro B. |
---|---|
Тема | Re: ... more than one count with left join |
Дата | |
Msg-id | 200602202056.25468.pedro.borracha@netcabo.pt обсуждение исходный текст |
Список | 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, |>
В списке pgsql-sql по дате отправления:
Предыдущее
От: "Mark R. Dingee" PedroДата:
Сообщение: Re: Given 02-01-2006 to 02-28-2006, output all days.