Group by multiple grouping sets summary--grouping Sets,group by Cube,group by Rollup,grouping (), grouping_id ()

T-SQL multiple grouping sets total three GROUPING sets, cube, and rollup, where cube and rollup can be used as a shorthand version of GROUPING sets

Sample database Download:

Http://files.cnblogs.com/files/haseo/TSQL2012.rar

GROUPING Sets

List all the grouping sets you set

SELECT  Year  as COUNT (* as numorders from sales.ordersGROUP by GROUPINGYear (ShippedDate)), ()); 

CUBE

List all possible grouping sets

SELECT   ShipperID,        year as  shipyear,        COUNT(*as  numordersfrom    sales.ordersGROUPby year (ShippedDate));

1. (ShipperID, year (ShippedDate)) 2. (ShipperID) 3. (Year (ShippedDate)) 4. ( ) RoolupList grouping sets in a hierarchical fashion

SELECT   ShipCountry,        shipregion,        shipcity,        COUNT(* as numorders  from    sales.ordersGROUP by ROLLUP (ShipCountry, ShipRegion, ShipCity);

1. (ShipCountry, ShipRegion, shipcity) 2. (ShipCountry, ShipRegion) 3. (ShipCountry) 4. ( )

GROUPING ()
This function is used to distinguish whether an element that is brought in is part of a grouping, returns 0 for a, and 1 means that it does not belong to

SELECTShipCountry,GROUPING(ShipCountry) asgrpcountry, ShipRegion,GROUPING(shipregion) asgrpcountry, ShipCity,GROUPING(shipcity) asGrpcountry,COUNT(*) asnumorders fromsales.ordersGROUP  byROLLUP (ShipCountry, ShipRegion, shipcity);

GROUPING_ID ()

The function returns the bitmap of the grouped column (the little partner who learns the binary knows, 8421 ... and so on), and if it is 0, all the grouping fields are part of the grouping, and if a grouping field is not part of the grouping set, the corresponding number (both related to binary position 1) is returned, and finally summarized.

The following code, if ShipCountry, ShipRegion, ShipCity is part of the grouping, returns 0. If shipregion, ShipCity is not within the group, it is 3 (0+2+1)

SELECT   as grp_id,        ShipCountry,        shipregion,        shipcity,        COUNT(* as numorders  from     sales.ordersGROUP by ROLLUP (ShipCountry, ShipRegion, shipcity);

Group by multiple grouping sets summary--grouping Sets,group by Cube,group by Rollup,grouping (), grouping_id ()