Re: Q: using generate_series to fill in the blanks

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;