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;