SQL Server/T-SQL Tutorial/Analytical Functions/GROUPING
Содержание
GROUPING() Comes to the Rescue
You use the GROUPING() function with an expression that might contain NULLs.
GROUPING() function returning value is only relevant when the value is NULL.
GROUPING() function returns 1 when the NULL represents a super aggregate.
GROUPING() function returns 0 when the NULL represents a group of NULLs in the base table.
CUBE Query and the GROUPING() function
10>
11> CREATE TABLE employee(
12> id INTEGER NOT NULL PRIMARY KEY,
13> first_name VARCHAR(10),
14> last_name VARCHAR(10),
15> salary DECIMAL(10,2),
16> start_Date DATETIME,
17> region VARCHAR(10),
18> city VARCHAR(20),
19> managerid INTEGER
20> );
21> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2>
3>
4> SELECT
5> ID,
6> GROUPING(ID) AS Grp_Cust,
7> YEAR(Start_Date) AS Order_Year,
8> GROUPING(YEAR(Start_Date)) AS Grp_Year,
9> COUNT(*) as Order_Count
10> FROM
11> Employee
12> GROUP BY
13> ID,
14> YEAR(Start_Date)
15> WITH CUBE
16> GO
ID Grp_Cust Order_Year Grp_Year Order_Count
----------- -------- ----------- -------- -----------
1 0 2005 0 1
1 0 NULL 1 1
2 0 2003 0 1
2 0 NULL 1 1
3 0 2001 0 1
3 0 NULL 1 1
4 0 2006 0 1
4 0 NULL 1 1
5 0 2004 0 1
5 0 NULL 1 1
6 0 2002 0 1
6 0 NULL 1 1
7 0 2008 0 1
7 0 NULL 1 1
8 0 2007 0 1
8 0 NULL 1 1
9 0 2001 0 1
9 0 NULL 1 1
NULL 1 NULL 1 9
NULL 1 2001 0 2
NULL 1 2002 0 1
NULL 1 2003 0 1
NULL 1 2004 0 1
NULL 1 2005 0 1
NULL 1 2006 0 1
NULL 1 2007 0 1
NULL 1 2008 0 1
(27 rows affected)
1>
2> drop table employee;
3> GO
GROUPING() function returns a bit value (1 or 0) to indicate that a row is a rollup.
This makes it easy to separate the aggregation of null values.
6>
7> CREATE TABLE employee(
8> id INTEGER NOT NULL PRIMARY KEY,
9> first_name VARCHAR(10),
10> last_name VARCHAR(10),
11> salary DECIMAL(10,2),
12> start_Date DATETIME,
13> region VARCHAR(10),
14> city VARCHAR(20),
15> managerid INTEGER
16> );
17> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2>
3>
4> SELECT Region,GROUPING(Region) As SO_Group
5> FROM Employee
6> GROUP BY Region
7> WITH ROLLUP;
8> GO
Region SO_Group
---------- --------
East 0
North 0
South 0
West 0
NULL 1
(5 rows affected)
1>
2> drop table employee;
3> GO
1>
2>
Replacing NULLs with ALL and UNKNOWN
4>
5> CREATE TABLE employee(
6> id INTEGER NOT NULL PRIMARY KEY,
7> first_name VARCHAR(10),
8> last_name VARCHAR(10),
9> salary DECIMAL(10,2),
10> start_Date DATETIME,
11> region VARCHAR(10),
12> city VARCHAR(20),
13> managerid INTEGER
14> );
15> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2>
3>
4> CREATE VIEW Vbase_cube
5> AS
6> SELECT
7> ID,
8> GROUPING(ID) AS Grp_Cust,
9> YEAR(Start_Date) AS Order_Year,
10> GROUPING(YEAR(Start_Date)) AS Grp_Year,
11> COUNT(*) as Order_Count
12> FROM
13> Employee
14> GROUP BY
15> ID,
16> YEAR(Start_Date)
17> WITH CUBE
18> GO
1> --Selecting All Rows from the Vbase_cube View
2> SELECT
3> *
4> FROM
5> Vbase_cube
6> GO
ID Grp_Cust Order_Year Grp_Year Order_Count
----------- -------- ----------- -------- -----------
1 0 2005 0 1
1 0 NULL 1 1
2 0 2003 0 1
2 0 NULL 1 1
3 0 2001 0 1
3 0 NULL 1 1
4 0 2006 0 1
4 0 NULL 1 1
5 0 2004 0 1
5 0 NULL 1 1
6 0 2002 0 1
6 0 NULL 1 1
7 0 2008 0 1
7 0 NULL 1 1
8 0 2007 0 1
8 0 NULL 1 1
9 0 2001 0 1
9 0 NULL 1 1
NULL 1 NULL 1 9
NULL 1 2001 0 2
NULL 1 2002 0 1
NULL 1 2003 0 1
NULL 1 2004 0 1
NULL 1 2005 0 1
NULL 1 2006 0 1
NULL 1 2007 0 1
NULL 1 2008 0 1
(27 rows affected)
1>
2> SELECT
3> ISNULL(CAST(ID AS varchar(7)),
4> CASE Grp_Cust
5> WHEN 1 THEN "ALL"
6> ELSE "UNKNOWN"
7> END) AS Customer,
8> ISNULL(CAST(Order_Year AS varchar(7)),
9> CASE Grp_Year
10> WHEN 1 THEN "ALL"
11> ELSE "UNKNOWN"
12> END) AS Order_Year,
13> Order_Count
14> FROM
15> Vbase_cube
16> GO
Customer Order_Year Order_Count
-------- ---------- -----------
1 2005 1
1 ALL 1
2 2003 1
2 ALL 1
3 2001 1
3 ALL 1
4 2006 1
4 ALL 1
5 2004 1
5 ALL 1
6 2002 1
6 ALL 1
7 2008 1
7 ALL 1
8 2007 1
8 ALL 1
9 2001 1
9 ALL 1
ALL ALL 9
ALL 2001 2
ALL 2002 1
ALL 2003 1
ALL 2004 1
ALL 2005 1
ALL 2006 1
ALL 2007 1
ALL 2008 1
(27 rows affected)
1>
2> drop view Vbase_cube
3> GO
1> drop table employee;
2> GO
The syntax of the GROUPING function: GROUPING(column_name)
18> --A summary query that uses the GROUPING function
21> create table Bankers(
22> BankerID Integer,
23> BankerName VARCHAR(20),
24> BankerContactLName VARCHAR(20),
25> BankerContactFName VARCHAR(20),
26> BankerCity VARCHAR(20),
27> BankerState VARCHAR(20),
28> BankerZipCode VARCHAR(20),
29> BankerPhone VARCHAR(20)
30> )
31> GO
1>
2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111");
3> GO
(1 rows affected)
1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222");
2> GO
(1 rows affected)
1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333");
2> GO
(1 rows affected)
1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444");
2> GO
(1 rows affected)
1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555");
2> GO
(1 rows affected)
1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666");
2> GO
(1 rows affected)
1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777");
2> GO
(1 rows affected)
1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888");
2> GO
(1 rows affected)
1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999");
2> GO
(1 rows affected)
1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000");
2> GO
(1 rows affected)
1>
2>
3>
4>
5> SELECT
6> CASE
7> WHEN GROUPING(BankerState) = 1 THEN "All"
8> ELSE BankerState
9> END AS BankerState,
10> CASE
11> WHEN GROUPING(BankerCity) = 1 THEN "All"
12> ELSE BankerCity
13> END AS BankerCity,
14> COUNT(*) AS QtyBankers
15> FROM Bankers
16> WHERE BankerState IN ("IA", "NJ")
17> GROUP BY BankerState, BankerCity WITH ROLLUP
18> ORDER BY BankerState DESC, BankerCity DESC
19> GO
BankerState BankerCity QtyBankers
-------------------- -------------------- -----------
(0 rows affected)
1>
2> drop table Bankers;
3> GO
1>
2>