oralce GROUPING

/*从上面的结果中我们很容易发现,每个统计数据所对应的行都会出现null,

如何来区分到底是根据那个字段做的汇总呢,grouping函数判断是否合计列!*/

 

select decode(grouping(id),1,'all id',id) id,

decode(grouping(area),1,'all area',to_char(area)) area,

decode(grouping(stu_type),1,'all_stu_type',stu_type) stu_type,

sum(score) score

from students

group by cube(id,area,stu_type)

order by id,area,stu_type;