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

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: 25 Aug 2003 05:34:44 -0700
Message-ID: <1a75df45.0308250434.389c7712@posting.google.com>

richardshea_at_fastmail.fm (Richard Shea) wrote

> select * from tblValues
>
> ... the output looks like this ...
>
> DESC VALUE
> ==== =====
> A 10
> B 21
> C 8
> D 22
> E 9
> F 98
>
> ... but how can I write a query which will group all rows with a value
> less then X into a 'Miscellanous' row ? That is so that the output
> (for X = 20)would look like this ...
>
> DESC VALUE
> ==== =====
> B 21
> D 22
> F 98
> MISC 27
First the sub-select on its own so that you can see how the logic works.

The GROUP_REF contains a 0/1 (TRUE/FALSE) value that indicates if the OBJECT_COUNT should be summed in the MISC group or not.

Note that the number 1000 in GROUP_REF indicates your value X. I.e. we want all counts below a 1000 to be aggregated into the MISC grouping.

SQL> select
  2 object_type,
  3 count(*) OBJECT_COUNT,   4 TRUNC(count(*)/1000) GROUP_REF
  5 from all_objects
  6 group by object_type
  7 /

OBJECT_TYPE OBJECT_COUNT GROUP_REF

------------------ ------------ ----------
CLUSTER                       1          0
CONSUMER GROUP                2          0
CONTEXT                       3          0
DIRECTORY                     2          0
FUNCTION                    164          0
INDEX                      1423          1
JAVA CLASS                14260         14
JAVA DATA                   291          0
JAVA RESOURCE               196          0
JAVA SOURCE                   3          0
LIBRARY                      24          0
PACKAGE                     372          0
PACKAGE BODY                350          0
PROCEDURE                   227          0
SEQUENCE                    492          0
SYNONYM                    8008          8
TABLE                      1641          1
TRIGGER                     480          0
TYPE                        109          0
TYPE BODY                     1          0
VIEW                       1508          1

21 rows selected.

Okay, now we use this as an in-line view and we add very simplistic logic to it:
IF GROUP_REF = 0 THEN
  grouped it under 'MISC'
ELSE
  group it as OBJECT_TYPE
END-IF This logic is implemented with the DECODE statement. The CASE statement should make this look more readable. You will the both explained in the Oracle SQL Reference Manual.

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 /

OBJECT_TYPE OBJECT_COUNT

------------------ ------------
INDEX                      1423
JAVA CLASS                14260
MISC                       2717
SYNONYM                    8008
TABLE                      1641
VIEW                       1508

6 rows selected.   

--
Billy

Received on Mon Aug 25 2003 - 07:34:44 CDT