Re: How to get different columns total in one time query?

RK schrieb:
> Hi, can someone help me, please?
>
> I have a big table with two different fields f1 and f2, there are many
> many different combinations of these two fields values.
>
> Now I need to do this:
>
> select f1, count(*) from table group by f1;
> select f2, count(*) from table group by f2;
>
> Each count may take long time. Can I run one query on the two fields,
> and still get separate total counts of the different field-values, with
> SQL*Plus only?
>
> Thanks.
>

If you are on 9i and above, using of grouping sets just gives you desired subtotatls:

SQL> col owner for a15
SQL> col object_type for a15
SQL> select owner,object_type, count(*) from all_objects

   2 group by grouping sets(owner,object_type)    3 /

OWNER OBJECT_TYPE COUNT(*) --------------- --------------- ----------

CCV_REPL_OT                            133
CCV_REPL_TEST                          135
CTXSYS                                 261
HR                                      34
MD                                    1693
MDSYS                                  235
MDWORK                                 101
MMM                                     22
NON_DBA                                  1
ODM                                    443
ODM_MTR                                 12

OWNER           OBJECT_TYPE       COUNT(*)
--------------- --------------- ----------
OE                                      86
OLAPSYS                                676
ORDPLUGINS                              28
ORDSYS                                 972
OUTLN                                    7
PERFSTAT                                86
PM                                       9
PUBLIC                               12565
QS                                      41
QS_ADM                                   7
QS_CBADM                                24

OWNER           OBJECT_TYPE       COUNT(*)
--------------- --------------- ----------
QS_CS                                   23
QS_ES                                   39
QS_OS                                   39
QS_WS                                   39
SCOTT                                   12
SH                                     174
SYS                                  13932
SYSTEM                                 384
WKSYS                                  281
WMSYS                                  129
XDB                                    270

OWNER           OBJECT_TYPE       COUNT(*)
--------------- --------------- ----------
                 CONSUMER GROUP           2
                 CONTEXT                  2
                 DIRECTORY                5
                 EVALUATION CONT         11
                 EXT

                 FUNCTION               103
                 INDEX                 1194
                 INDEX PARTITION        932
                 INDEXTYPE                8
                 JAVA CLASS           10261

OWNER           OBJECT_TYPE       COUNT(*)
--------------- --------------- ----------
                 JAVA DATA              293
                 JAVA RESOURCE          195
                 JAVA SOURCE             16
                 LIBRARY                 90
                 LOB                      7
                 LOB PARTITION          402
                 MATERIALIZED VI         13
                 EW

                 OPERATOR                29
                 PACKAGE                599

OWNER           OBJECT_TYPE       COUNT(*)
--------------- --------------- ----------
                 PACKAGE BODY           542
                 PROCEDURE               50
                 RULE SET                15
                 SEQUENCE               141
                 SYNONYM              12585
                 TABLE                 1095
                 TABLE PARTITION        549
                 TRIGGER                114
                 TYPE                   961
                 TYPE BODY               54
                 VIEW                  2625

Best regards

Maxim Received on Wed Jun 08 2005 - 14:18:19 CDT