[Application of sql]-oracle grouping function Roullup

The first part explains:
Oracle's GROUP BY statement supports rollup and cube statements in addition to the most basic syntax. In the case of group by ROLLUP (A, B, c), the group by IS first performed on (A, B, c), then group by (A, B), then (a) group by, and finally the group by operation of the whole table.

Because the work to make a report, so want to run a report directly in the database out, take part of the company data collation as follows:

SELECTClp. ItemName asPorvince, CLC. ItemName asCity,SUM( Case  whenC2. Itemname=' Standard Products '  ThenC.businesssumELSE 0 END) asStandard Products,SUM( Case  whenC2. Itemname=' VIP products '  ThenC.businesssumELSE 0 END) asVIP Products,SUM(c.businesssum) asTotal fromBusiness_contract C Left JOINStore_info S onC.stores = S.sno Left JOINCode_library CLP onS.province = CLP. ItemNo andClp. Codeno =' Provincecodebyhand '   Left JOINCode_library CLC onS.city = CLC. ItemNo andClc. Codeno =' Citycodebyhand '   Left JOINBusiness_type T onC.businesstype=t.typeno Left JOINCode_library C2 onT.promotiontype = C2. ItemNo andC2. Codeno =' Promotiontype 'WHEREClp. Itemname=' Guangdong province 'ORClp. Itemname=' Hunan Province 'GROUP  byClp. ITEMNAME,CLC. ItemName

Query Result:

Here you need to add a row at the end of the summary row, here can use the automatic summary function Roullup, with the group by, the following wording:

GROUP BY ROLLUP(CLP.ITEMNAME,CLC.ITEMNAME）

The results are as follows:

Will find that the function will have a small total in Guangdong province and Hunan province, and finally all the total, if only to make a total, you can use grouping_id to beautify, the code is as follows:

GROUP BY ROLLUP(CLP.ITEMNAME,CLC.ITEMNAME)HAVING grouping_id(CLP.ITEMNAME,CLC.ITEMNAME)!=1

The results are as follows:

Here is a description of grouping_id:
Total is grouping_id=3
Subtotal grouping_id=1
The record is grouping_id=0.

Above

[Application of sql]-oracle grouping function Roullup