Q: using generate_series to fill in the blanks

Поиск
Список
Период
Сортировка
От Ow Mun Heng
Тема Q: using generate_series to fill in the blanks
Дата
Msg-id 1196999064.6988.30.camel@neuromancer.home.net
обсуждение исходный текст
Ответы Re: Q: using generate_series to fill in the blanks  ("Rodrigo De León" <rdeleonp@gmail.com>)
Список 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 по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Continual Postgres headaches...
Следующее
От: "Rodrigo De León"
Дата:
Сообщение: Re: Q: using generate_series to fill in the blanks