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.

"RK" <rekaben@yahoo.com> wrote in
news:1118245120.906496.257230@f14g2000cwb.googlegr oups.com:

>
> 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?
>
Yes, but neither query above does what you say you want.
Both will give the same value for count.
You might want to try using the DISTINCT qualifier.

Take a look at the rollup and cube option on the group by.
Something like ...
Select f1,f2,count(*) from table group by cube(f1,f2);
or
Select f1,f2,f3,max(f4),count(*)
from table group by f1, f2, g3;

This last example is just to show you that you can (must) group by all
columns in the select clause which are not part of a aggregate
function, such as max or count, when you use the group by clause.

HTH

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