oracle统计

ROLLUP 使用 小计 合计
SELECT NVL(L.CHNLNAME, '合计') AS 栏目名称,
NVL(SUM(DECODE(T.CRUSER,'system',1)), 0) AS 采集量,
NVL(SUM(DECODE(T.DOCSTATUS,10,1)),0) AS 发布量
FROM WCMCHNLDOC C , WCMDOCUMENT T , WCMCHANNEL L , WCMWEBSITE E
WHERE T.DOCID = C.DOCID
AND C.CHNLID = L.CHANNELID
AND T.SITEID = E.SITEID
GROUP BY ROLLUP ( L.CHNLNAME )

grouping set使用
SELECT
WCMGRPUSER.USERID AS USERID,
NVL(WCMDOCUMENT.OPERUSER , '合计') AS PUBUSER ,
NVL(SUM(DECODE(WCMCHANNEL.SITEID, 4 ,1)),0) AS NJ,
SUM(1) AS AllDOCCOUNT
FROM WCMCHANNEL , WCMDOCUMENT , WCMCHNLDOC , WCMGRPUSER ,WCMUSER
WHERE (WCMDOCUMENT.DOCID = WCMCHNLDOC.DOCID
AND WCMCHNLDOC.CHNLID = WCMCHANNEL.CHANNELID
AND WCMGRPUSER.USERID = WCMUSER.USERID
AND WCMUSER.USERNAME = WCMDOCUMENT.OPERUSER
AND WCMCHNLDOC.DOCSTATUS > 0
AND WCMGRPUSER.GROUPID = 3
AND WCMCHNLDOC.CRTIME >= to_date('2010-02-20 00:00:00','YYYY-MM-DD HH24:MI:SS')
AND WCMCHNLDOC.CRTIME <= to_date('2011-02-24 23:59:59','YYYY-MM-DD HH24:MI:SS')
)
GROUP BY grouping sets(( WCMDOCUMENT.OPERUSER , WCMGRPUSER.USERID ) ,null)
ORDER BY WCMDOCUMENT.OPERUSER ASC