Re: a row disapearing

Maybe this my work for you?

CREATE TABLE xxx (id int, info text);
create table xxx_grp ( id int, grp text);

insert INTO xxx_grp values ( 0, 'group0');
insert INTO xxx_grp values ( 1, 'group1');
insert INTO xxx_grp values ( 2, 'group2');

insert into xxx values ( 1, 'test1');
insert into xxx valves ( 2, 'test2a');
insert into xxx values ( 2, 'test2b');

select count(xxx.id) as cnt,
xxx.id,
xxx_grp.id as grpid,
xxx_grp.grp
from xxx
right join xxx_grp
on (xxx.id = xxx_grp.id)
group by xxx.id, grpid, xxx_grp.grp
order by xxx_grp.id;

cnt | id | grpid | grp
-----+----+-------+--------
0 | | 0 | group0
1 | 1 | 1 | group1
2 | 2 | 2 | group2

regards,

Richard Broersma Jr.

--- Rafal Pietrak <rafal(at)zorro(dot)isa-geek(dot)com> wrote:

> On Sat, 2006-05-27 at 14:06 -0400, Bruce Momjian wrote:
> > > Is this a feature, or a bug? And in fact, is there a construct to get
> > > both the count() and its selectors *in*case*, when the count is ZERO?
> > >
> > > All the above in postgres 8.1.
> >
> > It is supposed to work that way. In the first query, we have to return
> > a row to show you the count, while in the second query, there is no 'id'
> > value to show you, so we return nothing (nothing to GROUP BY).
>
> But is there a way to achieve one row output with both the count() and
> its selector, when the ocunt is ZERO?
>
> I'm digging this, because it looke like I need a VIEW, that returns such
> count() no matter what. And in fact the selector (which is coming from
> subquery) is more important for me in that case, than the count() itself
> (well, I need to distinquish zero from something, but nothing more).
>
> Is there a way to see it?
>
> --
> -R
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>