From: | Ow Mun Heng <Ow(dot)Mun(dot)Heng(at)wdc(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Q: using generate_series to fill in the blanks |
Date: | 2007-12-07 03:44:24 |
Message-ID: | 1196999064.6988.30.camel@neuromancer.home.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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]
.....
From | Date | Subject | |
---|---|---|---|
Next Message | Rodrigo De León | 2007-12-07 04:06:56 | Re: Q: using generate_series to fill in the blanks |
Previous Message | Tom Lane | 2007-12-07 01:00:56 | Re: Continual Postgres headaches... |