Re: 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(dot)05pal(at)gmail(dot)com> wrote:
>
> 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.......
>

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(dot)wagner(at)gmail(dot)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(at)gmail(dot)com>

On 1/23/07, Jorge Godoy <jgodoy(at)gmail(dot)com> wrote:
>
> > 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;
>

That's one of the problems with having a couple dozen date/time functions
;).

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