Обсуждение: show all record between two date after group by and aggrigation...

i am fatching record's from data base between two date range for registration_date coloum and than group by an count it using count(registration_date) i have to show all dates even if date is not there in registration_date ,it should show date and 0 in count.,how can i do it plz healp.......

On 1/22/07, deepak pal <deepak.05pal@gmail.com> wrote:
Most people do this with a "calendar" table, worst case is you could use generate_series to do it (but it's ugly, and it may not scale well -- haven't tested it) and left join it to your data table.

select cal.date, coalesce(foo.x, 0) AS x
  from (select (date_trunc('day', current_timestamp) + (s.s * interval '1 day'))::date AS date
          from generate_series(1,365) AS s) AS cal
left join foo ON cal.date = foo.create_date;

    date    | x 
------------+----
 2007-01-23 |  1
 2007-01-24 |  0
 2007-01-25 |  2
 2007-01-26 |  3
 2007-01-27 |  0
 2007-01-28 |  4
 2007-01-29 |  5
 2007-01-30 |  0
 2007-01-31 |  6
 2007-02-01 |  0
 2007-02-02 |  7
 2007-02-03 |  8
 2007-02-04 |  0
 2007-02-05 |  9
 2007-02-06 |  0
 2007-02-07 | 10
 2007-02-08 |  0
 2007-02-09 | 11
 2007-02-10 |  0
 2007-02-11 | 12
 2007-02-12 |  0
 2007-02-13 | 13
 2007-02-14 |  0



--
Chad
http://www.postgresqlforums.com/

"Chad Wagner" <chad.wagner@gmail.com> writes:

> Most people do this with a "calendar" table, worst case is you could use
> generate_series to do it (but it's ugly, and it may not scale well --
> haven't tested it) and left join it to your data table.
>
> select cal.date, coalesce(foo.x, 0) AS x
>  from (select (date_trunc('day', current_timestamp) + (s.s * interval '1
> day'))::date AS date
>          from generate_series(1,365) AS s) AS cal
> left join foo ON cal.date = foo.create_date;

Why not, for the date part, the simpler alternative:

SELECT CURRENT_DATE + s.s AS DATE FROM generate_series(1, 365) AS s;

?


Be seeing you,
--
Jorge Godoy      <jgodoy@gmail.com>

On 1/23/07, Jorge Godoy <jgodoy@gmail.com> wrote:
That's one of the problems with having a couple dozen date/time functions ;).


--
Chad
http://www.postgresqlforums.com/