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(dot)Mun(dot)Heng(at)wdc(dot)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..