Thread: Q: using generate_series to fill in the blanks
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
the query in psql is something like this..
select vdt, count(*) from footable where c_id = '71' group by vdt order
by vdt
problem is.. since there's not data whatsoever on vdt=4 I get this..
vdt | count
------------+-------
1 | 514
2 | 27
3 | 15
5 | 12
6 | 15
I tried to use generate_series
select generate_series(1,7,1), count(*) from footable where c_id = '71'
group by generate_series(1,7,1),vdt order by generate_series(1,7,1);
(note : the vdt are numbered from 1 to 7 sequence)
generate_series | count
-----------------+-------
1 | 514
1 | 27
1 | 15
1 | 12
1 | 15
2 | 514
2 | 27
2 | 15
2 | 12
2 | 15
3 | 514
3 | 27
3 | 15
3 | 12
3 | 15
4 | 514
4 | 27
4 | 15
4 | 12
4 | 15
.....
[snip]
.....
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;
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..
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