Re: SQL Gurus - Producing 'Misc' row only when necessary ?

From: Anurag Varma <avarmadba.skipthis_at_yahoo.com>
Date: Mon, 25 Aug 2003 16:51:46 GMT
Message-ID: <C8r2b.6293$Jq1.2277@nwrddc03.gnilink.net>

"Billy Verreynne" <vslabs_at_onwe.co.za> wrote in message news:1a75df45.0308250434.389c7712_at_posting.google.com... --snip--
>
> SQL> select
> 2 DECODE( group_ref, 0, 'MISC',
> 3 object_type ) OBJECT_TYPE,
> 4 SUM( object_count) OBJECT_COUNT
> 5 from (
> 6 select
> 7 object_type,
> 8 count(*) OBJECT_COUNT,
> 9 TRUNC(count(*)/1000) GROUP_REF
> 10 from all_objects
> 11 group by object_type
> 12 )
> 13 group by
> 14 DECODE( group_ref, 0, 'MISC',
> 15 object_type )
> 16 /

-snip--
> --
> Billy

Just to add to this ... You can also use SIGN function to determine this easily.

select decode(sign(mycount - 27), 1, group_name, 'MISC' ) group_name,

sum(mycount)
from (select group_name, count(*) mycount from tbl) group by decode( sign(mycount - 27), 1, group_name, 'MISC' ) /

... Case function makes this more readable and easier to implement (if available in OP's version)

Anurag Received on Mon Aug 25 2003 - 11:51:46 CDT