Examples of GROUPING_ID Function
To understand the GROUPING ID function, We need a Table on which we will perform various operations and queries. So here we have
IndianEmployees Table which consist of EmployeeID, FirstName, LastName, Department, City and Salary as Columns. After Inserting some data into the table, The table looks:
Example 1: Simple Use of GROUPING_ID Function
Let’s Determine the number of employees and average salary within each department, as well as the overall company-wide totals, to gain insights into employee distribution and compensation across different departments.
Query:
SELECT
GROUPING_ID(Department) AS GroupingLevel,Department,
COUNT(*) AS EmployeeCount,
AVG(Salary) AS AvgSalary
FROM IndianEmployees
GROUP BY
GROUPING SETS ((Department), ());
Output:
Explanation: In the following example, We have shown the basic use of GROUPING_ID function to identify the grouping levels in a table. We have grouped the data by the column department and calculated the average salary of each department using the aggregate function AVG(). The Grouping level of the last record is 1 as compared to other records because it denotes the total sum of all the departments in the table which is at a higher hierarchy as compared to others.
Example 2: GROUPING_ID Function with Multiple Columns
Let’s Count employees and see their average salary in both departments and cities. Also, check the same for only departments and ignoring cities. Then, find out total employees and their average salary across the whole company.
Query:
SELECT
GROUPING_ID(Department, City) AS GroupingLevel,
COUNT(*) AS EmployeeCount,
AVG(Salary) AS AvgSalary
FROM IndianEmployees
GROUP BY
GROUPING SETS ((Department, City), (Department), ());
Output:
Explanation: In the following example, We have learnt the use of GROUPING_ID function along with multiple columns in the SQL server. The query groups the result set based on two columns Department and City and displays how GROUPING_ID function handles grouping of multiple levels. The last records has the highest grouping level because it groups the whole data and give the average salary of the total employees present in the table.
Example 3: GROUPING_ID Function with WHERE Clause
Suppose We want to find out those employees who are making over 80,000 rupee, and how much they earn on average in different departments and cities, and the overall distribution of high salaries across the company.
Query:
SELECT
GROUPING_ID(Department, City) AS GroupingLevel,
COUNT(*) AS EmployeeCount,
AVG(Salary) AS AvgSalary
FROM IndianEmployees
WHERE Salary > 80000
GROUP BY
GROUPING SETS ((Department, City), (Department), ());
Output:
Explanation: In this example the GROUPING_ID function is used to calculate the grouping levels of data that is grouped using multiple columns and WHERE clause is used to filters the result set. All the records having the salary field greater than 80000 are only considered in the final result set. All the other records are filtered out from the result set using the WHERE clause.
Example 4: GROUPING_ID Function with SUM Function
Let us consider the following dummy table Sales on which we will perform our next query.
Query:
Let’s Analyze sales performance by product, region, and overall to understand sales distribution, regional variations, and product-specific contributions to revenue.
SELECT
GROUPING_ID(Product, Region) AS GroupingLevel,Product,Region,
COUNT(*) AS SalesCount,
SUM(SalesAmount) AS TotalSales
FROM
Sales1
GROUP BY
GROUPING SETS ((Product, Region), (Product), ());
Output:
Explanation: In this example, We have used the GROUPING function to generate subtotals and totals for both the Product and Regions field. The aggregate function SUM() is used to calculate the total sales amount. The records with NULL values denotes the grouped result of a region or multiple regions. The last records has the highest grouping level 3 because it denotes the total sales amount of both the products in the table. The fields with grouping level 1 denotes the total sales amount of each product in all the regions combined. The fields with grouping level 0 is at the lowest level which denotes the total sales amount of each product in each separate region.
GROUPING ID Function in SQL Server
SQL Server is a Relational Database Management System that is used to create and manipulate the database. It provides advanced security measures like encryption, access control, and auditing to protect sensitive data from unauthorized access.
It Supports a wide range of data types, including structured, semi-structured, and unstructured data, for effective data storage and management. It also Supports various programming languages and offers tools like SQL Server Management Studio for simplified database administration and development.
In this article, we are going to learn about the GROUPING_ID() Function in SQL Server. We will learn how we can implement the use of this function through various practical examples. We will learn how this function can be used with single as well as multiple columns in the SQL server.
Contact Us