oracle中统计有效数据,oracle中的数据统计

一、使用ROLLUP和CUBE汇总统计

1、测试数据

create table A

(

GRADE CHAR(5),

ID    CHAR(5),

NUM   CHAR(5)

)

insert into a values('a',1,1);

insert into a values('a',2,2);

insert into a values('b',2,4);

insert into a values('b',3,4);

2、查看几个分组结果

SQL> select sum(num) from a; //全部求和

SUM(NUM)

----------

11

SQL> select grade,sum(num) from a group by grade;//按grade分组

GRADE   SUM(NUM)

----- ----------

a              3

b              8

SQL> select id,sum(num) from a group by id;;//按id分组

ID      SUM(NUM)

----- ----------

1              1

2              6

3              4

SQL> select id,grade,sum(num) from a group by grade,id;;//按grade,id分组

ID    GRADE   SUM(NUM)

----- ---