SQL - GROUPING_ID() Function

The SQL Grouping_ID() is the SQL function which is used to compute the level of grouping. It can only be used with SELECT statement, HAVING clause, or ORDERED BY clause when GROUP BY is specified.

The GROUPING_ID() function returns an integer bitmap with the lowest N bits illuminated. A illuminated bit indicates that the corresponding argument is not a grouping column for the specified output row. Argument N is represented by the lowest-order bit. i.e. 0, and argument 1 is represented by the N-1th lowest-order bit.

Where, 0 specifies an element that is part of grouping, and 1 specifies that the element is not part of the grouping set.

Syntax

Following is the syntax of the SQL GROUPING_ID() function −

SELECT GROUPING_ID([column1, column2, ..., columnN])
FROM table_name/source
GROUP BY column1, column2, ..., columnN ;

The GROUPING_ID column expression is must exactly match the expression in GROUP BY list.

Example

In the following example, we are demonstrating the GROUPING_ID() function and displaying the grouping level from the customers table. First of all let us create a CUSTOMERS table using the following query −

CREATE TABLE customers(ID INT NOT NULL, 
NAME VARCHAR(30) NOT NULL, 
AGE INT NOT NULL, 
ADDRESS CHAR(30), 
SALARY DECIMAL(18, 2));

The table stores the ID, NAME, AGE, ADDRESS, and SALARY. Now we are inserting the 7 records in the customers table using the INSERT statement.

INSERT INTO customers VALUES(1, 'Ramesh', 32, 'Ahmedabad', 2000.00);
INSERT INTO customers VALUES(2, 'Khilan', 25, 'Delhi', 1500.00);
INSERT INTO customers VALUES(3, 'kaushik', 23, 'Kota', 2000.00);
INSERT INTO customers VALUES(4, 'Chaitali', 25, 'Mumbai', 6500.00);
INSERT INTO customers VALUES(5, 'Hardik', 27, 'Bhopal', 8500.00);
INSERT INTO customers VALUES(6, 'Komal', 22, 'MP', 4500.00);
INSERT INTO customers VALUES(7, 'Aman', 23, 'Ranchi', null);
The customers table will be as follows − 
+----+----------+-----+-----------+---------+
| ID | NAME     | AGE | ADDRESS   | SALARY  |
+----+----------+-----+-----------+---------+
|  1 | Ramesh   |  32 | Ahmedabad | 2000.00 |
|  2 | Khilan   |  25 | Delhi     | 1500.00 |
|  3 | kaushik  |  23 | Kota      | 2000.00 |
|  4 | Chaitali |  25 | Mumbai    | 6500.00 |
|  5 | Hardik   |  27 | Bhopal    | 8500.00 |
|  6 | Komal    |  22 | MP        | 4500.00 |
|  7 | Aman     |  23 | Ranchi    |    NULL |
+----+----------+-----+-----------+---------+

Following SQL query to display the ID, and AGE of the customers along with grouping level −

SELECT
   ID, AGE, GROUPING_ID(ID, AGE) AS Level
   FROM customers
   GROUP BY CUBE(ID, AGE);

Output

Following is the output of the above SQL query −

+------+------+--------+
|   ID |  AGE |  Level |
+------+------+--------+
|    6 |   22 |      0 |
| NULL |   22 |      2 |
|    3 |   23 |      0 |
|    7 |   23 |      0 |
| NULL |   23 |      2 |
|    2 |   25 |      0 |
|    4 |   25 |      0 |
| NULL |   25 |      2 |
|    5 |   27 |      0 |
| NULL |   27 |      2 |
|    1 |   32 |      0 |
| NULL |   32 |      2 |
| NULL | NULL |      3 |
|    1 | NULL |      1 |
|    2 | NULL |      1 |
|    3 | NULL |      1 |
|    4 | NULL |      1 |
|    5 | NULL |      1 |
|    6 | NULL |      1 |
|    7 | NULL |      1 |
+------+------+--------+

Example

In the following example, we display the grouping level of two columns using the GROUPING_ID() function. Using the CREATE statement, we created another table named EMP_Details −

CREATE TABLE EMP_Details
(
   ID INT IDENTITY(1,1) PRIMARY KEY,
   FIRTSNAME VARCHAR(100) ,
   LASTNAME VARCHAR(100),
   LOCATION VARCHAR(100),
   DOB DATETIME,
   SALARY MONEY,
   DEPT INT
)

The table stores the ID, firstname, lastname, location, dob, SALARY, and dept. Now we are inserting 8 records in the EMP_Details table using the INSERT statement.

INSERT INTO EMP_Details VALUES
   ('AKASH','KALLURI','HYDERABAD','07-23-1989',24000,1),
   ('GANESH','CH','PUNE','07-23-1987',48000,1),
   ('RAHUL','KUMAR','HYDERABAD','07-23-1988',25000,1),
   ('VENKATESH','BODUPPALY','HYDERABAD','07-23-1986',32000,2),
   ('SURAJ','MD','HYDERABAD','07-23-1987',38000,2),
   ('GANGA','RAJAYAM','PUNE','05-26-1987',390000,2),
   ('VIVEK','KUMAR','CHENNAI','03-23-1986',47000,1),
   ('AMAN','KUMAR','DELHI','07-23-1988',33000,2);
following is the EMP_Details table − 
+----+-----------+----------+-----------+-------------------------+----------+------+
| ID | FIRTSNAME | LASTNAME | LOCATION  |                    DOB  |   SALARY | DEPT |
+----+-----------+----------+-----------+-------------------------+----------+------+
|  1 |     AKASH |  KALLURI | HYDERABAD | 1989-07-23 00:00:00.000 | 24000.00 |    1 |
|  2 |    GANESH |       CH |      PUNE | 1987-07-23 00:00:00.000 | 48000.00 |    1 |
|  3 |     RAHUL |    KUMAR | HYDERABAD | 1988-07-23 00:00:00.000 | 25000.00 |    1 |
|  4 | VENKATESH |BODUPPALY | HYDERABAD | 1986-07-23 00:00:00.000 | 32000.00 |    2 |
|  5 |     SURAJ |       MD | HYDERABAD | 1987-07-23 00:00:00.000 | 38000.00 |    2 |
|  6 |     GANGA |  RAJAYAM |      PUNE | 1987-05-26 00:00:00.000 |390000.00 |    2 |
|  7 |     VIVEK |    KUMAR |   CHENNAI | 1986-03-23 00:00:00.000 | 47000.00 |    1 |
|  8 |      AMAN |    KUMAR |     DELHI | 1988-07-23 00:00:00.000 | 33000.00 |    2 |
+----+-----------+----------+-----------+-------------------------+----------+------+

Following SQL query display the DEPT, YEAR, GRP_Level, and COUNT of the EMP_Details −

SELECT
DEPT,YEAR(DOB) AS YEAR, 
GROUPING_ID(DEPT,YEAR(DOB)) GRP_LEVEL, COUNT(*) AS COUNT
FROM EMP_Details
GROUP BY ROLLUP((DEPT),(YEAR(DOB)),LOCATION);

Output

+--------+-------+------------+--------+
|   DEPT |  YEAR |  GRP_Level |  COUNT |
+--------+-------+------------+--------+
|      1 |  1986 |          0 |      1 |
|      1 |  1986 |          0 |      1 |
|      1 |  1987 |          0 |      1 |
|      1 |  1987 |          0 |      1 |
|      1 |  1988 |          0 |      1 |
|      1 |  1988 |          0 |      1 |
|      1 |  1989 |          0 |      1 |
|      1 |  1989 |          0 |      1 |
|      1 |  NULL |          1 |      4 |
|      2 |  1986 |          0 |      1 |
|      2 |  1986 |          0 |      1 |
|      2 |  1987 |          0 |      1 |
|      2 |  1987 |          0 |      1 |
|      2 |  1987 |          0 |      2 |
|      2 |  1988 |          0 |      1 |
|      2 |  1988 |          0 |      1 |
|      2 |  NULL |          1 |      4 |
|   NULL |  NULL |          3 |      8 |
+------+------+---------------+--------+

Example

In the following example, we display the grouping level of three columns using the GROUPING_ID() function from the above EMP_details table −

Following is the SQL query which shows the last name, year of DOB, location, Grouping level −

SELECT
LASTNAME,YEAR(DOB) As YEAR, 
LOCATION, GROUPING_ID(LASTNAME, YEAR(DOB), LOCATION) GRP_Level
FROM EMP_Details
GROUP BY ROLLUP((LASTNAME),(YEAR(DOB)),LOCATION);

Output

Following is the output of the above SQL query −

+------------+-------+-----------+------------+
|   LASTNAME |  YEAR |  LOCATION |  GRP_Level |
+------------+-------+-----------+------------+
|  BODUPPALY |  1989 | HYDERABAD |          0 |
|  BODUPPALY |  1989 |      NULL |          1 |
|  BODUPPALY |  NULL |      NULL |          3 |
|         CH |  1987 |      PUNE |          0 |
|         CH |  1987 |      NULL |          1 |
|         CH |  NULL |      NULL |          3 |
|     KALLURI|  1989 | HYDERABAD |          0 |
|     KALLURI|  1989 |      NULL |          1 |
|    KALLURI |  NULL |      NULL |          3 |
|      KUMAR |  1986 |   CHENNAI |          0 |
|      KUMAR |  1986 |      NULL |          1 |
|      KUMAR |  1988 |     DELHI |          0 |
|      KUMAR |  1988 | HYDERABAD |          0 |
|      KUMAR |  1988 |      NULL |          1 |
|      KUMAR |  NULL |      NULL |          3 |
|         MD |  1987 | HYDERABAD |          0 |
|         MD |  1987 |      NULL |          1 |
|         MD |  NULL |      NULL |          3 |
|    RAJAYAM |  1987 |      PUNE |          0 |
|    RAJAYAM |  1987 |      NULL |          1 |
|    RAJAYAM |  NULL |      NULL |          3 |
|       NULL |  NULL |      NULL |          7 |
+------------+-------+-----------+------------+
sql-aggregate-functions.htm
Advertisements