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
