Re: HELP!: How to get 0 COUNT values from a join

In article <1993Aug24.093127.1892_at_ccc.govt.nz> andrew_at_ccc.govt.nz writes:

>Hi folks,
>
>I've tried:
>
>select
> source.mesh_source,
> count(*)
>from
> source,
> target
>where
> source.mesh_source = target.mesh_target
>group by
> source.mesh_source;
>
>What I want is:
>
>SOURCE.MESH_SOURCE COUNT(*)
>------------------ --------
>
>2134500 12
>2367000 34
>2891200 0 ( No corresponding values in "target" )
>2397900 219
>
Unfortunately, this is one of those things in Oracle. If you read the description of how Oracle does GROUP BYs really carefully, it preprocesses the SELECT with an ORDER BY into a temporary table. As no rows are returned by MESH_SOURCE value 2891200, it doesn't get included in the COUNT(*) when the GROUP BY is done.

This only happens because the GROUP BY needs an ORDER BY hence explaining why you can observe COUNT(*) of 0 with non-GROUP BY SELECTs.

Not all databases (frustratingly) operate in the same way. SQLBase, for instance, would give you what you want from the above SELECT.

The only way I know of doing this in Oracle is programatically.

Regards

-- 
Chris Jack