Q: using generate_series to fill in the blanks

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]
.....

Responses

Browse pgsql-general by date

  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...