Re: show all record between two date after group by and aggrigation...

"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>