Re: Q: using generate_series to fill in the blanks

On Thu, 2007-12-06 at 23:06 -0500, Rodrigo De León wrote:
> On Dec 6, 2007 10:44 PM, Ow Mun Heng <Ow.Mun.Heng@wdc.com> wrote:
> > I've got a desired output which looks something like this..
> >
> >  vdt        | count
> > ------------+-------
> >           1 |   514
> >           2 |    27
> >           3 |    15
> >           4 |  <NULL>
> >           5 |    12
> >           6 |    15
>
> SELECT i.i AS vdt,
>        CASE
>          WHEN COUNT(vdt)=0 THEN NULL
>          ELSE COUNT(vdt)
>        END AS COUNT
> FROM generate_series (1, 7) i
>      LEFT JOIN footable f ON i.i = f.vdt AND c_id = '71'
> GROUP BY i.i
> ORDER BY i.i;

This is _way_ cool. Thanks. However I still have some additional
questions.

as individual c_ids:
 vdt | c_id  | count
-----+-------+-------
   1 | 71    |   533
   2 | 71    |    30
   3 | 71    |    15
   4 | 71    |    10
   5 | 71    |    12
   6 | 71    |    15
   7 |       |

 vdt |c_id| count
-----+-------+-------
   1 | 48 |   217
   2 | 48 |    86
   3 | 48 |    46
   4 | 48 |    50
   5 | 48 |     4
   6 |    |
   7 |    |

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

additionally, if you don't mind, when I substitute

-->and c_id = '71'

with

--> where c_id = '71'

the nulls also disappears.

In any case, it seems to be working for _single_ c_id clauses..