Re: Q: using generate_series to fill in the blanks - Mailing list pgsql-general

On Fri, Dec 07, 2007 at 01:18:13PM +0800, Ow Mun Heng wrote:
> select i.i as vdt,dcm_evaluation_code as c_id
> , case when count(vdt_format) = 0 then NULL else count(vdt_format) end
> as count
> from generate_series(1,7) i
> left join footable f
> on i.i = f.vdt_format
> and c_id in ('71','48')
> group by c_id, i.i
> order by c_id,i.i;
>
> When Joined into 1 query
>  vdt | c_id  | count
> -----+-------+-------
>    1 | HMK71 |   533
>    2 | HMK71 |    30
>    3 | HMK71 |    15
>    4 | HMK71 |    10
>    5 | HMK71 |    12
>    6 | HMK71 |    15 << What happened to 7?
>    1 | HML48 |   217
>    2 | HML48 |    86
>    3 | HML48 |    46
>    4 | HML48 |    50
>    5 | HML48 |     4
>    7 |       |

You need to start by generating all of the values you consider you
want.  In the previous example this was easy as all you wanted was a
set of numbers.  Now you want the cartesian product of this series and
something else.  So you need to be doing something like:

  SELECT x.i, x.j, COUNT(t.k)
  FROM (SELECT DISTINCT t.i,s.j FROM table t, generate_series(1,7) s(j)) x
    LEFT JOIN table t ON (x.i,x.j) = (t.i,t.j)
  GROUP BY x.i, x.j
  ORDER BY x.i, x.j;


  Sam