Re: a row disapearing

[Richard Broersma Jr <rabroersma(at)yahoo(dot)com>]

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
>