Re: Q: using generate_series to fill in the blanks

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