Q: using generate_series to fill in the blanks - Mailing list pgsql-general

From Ow Mun Heng
Subject Q: using generate_series to fill in the blanks
Date
Msg-id 1196999064.6988.30.camel@neuromancer.home.net
Whole thread Raw
Responses Re: Q: using generate_series to fill in the blanks
List 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]
.....

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Continual Postgres headaches...
Next
From: "Rodrigo De León"
Date:
Subject: Re: Q: using generate_series to fill in the blanks