Re: a row disapearing

Rafal Pietrak 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?

SELECT dummy.id, count(xxx.id) FROM (SELECT :id as id FROM dual) as
dummy LEFT JOIN xxx using (id) GROUP BY id;

You owe the Oracle a natural left join replacement.

/Nis