SqlTutorial-1 : Lesson-5 : Class-2 Microsoft SQL Server Training: Free Online-Learning Classes (OLC) for T-SQL Advanced-GROUPING-BY: COMPUTE, ROLLUP|CUBE, PARTITION, NTILE

INDEX

1. N-Tile()
2. Over ( PARTITION-clause) 
3. COMPUTE-BY Statement
4. GROUP BY with CUBE | ROLL-UP
5. GROUPING()

1. N-TILE

It creates N-Number of groups by deviding the resultset into N-number of groups. Each group is identified by a serial number.

Syntax:

NTILE(integer_exp) OVER( [ <partition_by_clause> ] < order_by_clause >)

Examples

SELECT region, NTILE(4)     over(order by region) FROM Employee		SELECT region, NTILE(3)   over(order by region) FROM Employee		SELECT region, NTILE(1)    over(order by region) FROM Employee
OUTPUT		OUTPUT		OUTPUT
Region		Region		Region
East	1	East	1	East	1
East	1	East	1	East	2
North	1	North	1	North	3
North	2	North	2	North	4
North	2	North	2	North	5
South	3	South	2	South	6
South	3	South	3	South	7
West	4	West	3	West	8
West	4	West	3	West	9

NTILE(No.of rows)= Row_NUMBER(), see NTILE(1) example of above.

2. Over (PARTITION-clause)

Using Over(Partition) clause you can do two thing:

1. You can apply Aggregate functions on different columns simultaneously in single SELECT.
2. You can SELECT columns with Aggregate functions without GROUP-BY clause.

Example:

USE AdventureWorks;
GO

SELECT SalesOrderID, ProductID, OrderQty ,
   SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS Total,
   AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Avg" ,
   COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Count" ,
   MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Min" ,
   MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Max"
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664);

OUTPUT :

3. COMPUTE-BY Statement

• It adds summary report computed with aggregate function to result set.
• Used to generate additional summary at the end of the result set.
• The beauty of COMPUTE is that you can nest multiple aggregate function in single statement. e.g,  COMPUTE MIN(SUM(salary));

1. The result of Query containing COMPUTE is invisible using Visual Studio, use Sqlcmd instead.
2. BY clause is optional, if present then ORDER BY clause with same expression must exists, otherwise it is error.
3. COMPUTE is applied to result of all aggregate function. i.e, to all groups. Therefore, you can use it to find Minimal/maximum group.
4. COMPUTE BY is applied to each group(i.e, within group) not all groups(i.e, not among all group). Therefore, you can use it to find sub-totals of each groups.>/li>

Syntax:

COMPUTE { Aggregate Function1} ( expression ) } [ Ag.Func2 ,...n ]
        [ BY expression [ ,...n ] ]

Examples:

1. Find sum and average of salaries of employees.

   SELECT id, first_name, last_name, salary,
               start_Date, region, city, managerid
   FROM Employee
   COMPUTE SUM(salary), AVG(salary)
   GO
   
   OUTPUT:
   
   
   
It is similar to SELECT SUM(salary), AVG(salary). But not followed by Query res

2. Find the smallest salaried region

   SELECT SUM(salary) AS Expr1 FROM Employee GROUP BY region COMPUTE MIN(SUM(salary))

   OR

   SELECT MIN(t1) As Expr1, FROM (SELECT SUM(salary)      FROM Employee      GROUP BY region)

   
   OUTPUT:
   
   
   
   Invalid Solutions

   1. Invalid-1: The following raises an error because Aggregate function cannot take aggregate function as parameter

      SELECT Min(Sum(Salary)) -- Error.
      From Employee
      Group By Region

   2. Invalid-2: The following returns Minimum value in each region. But not minimal valued region in all regions.

      SELECT MIN(salary)
      From Employee
      Group By Region

3. Find the sub-totals with details for each regions. (using COMPTUE BY )

   
   
   COMPUTE BY is applied to each group not all groups as whole. Note that it must be followed by ORDER BY clause with same column.
   
   
   SELECT salary, region
   FROM employee
   ORDER BY region
   COMPUTE SUM(salary) by region
   
   OUTPUT
   

   

   
   

4. Group By with CUBE| ROLLUP
   ( a.k.a Grouping Sets Operators )

• CUBE and ROLLUPs are extentions to GROUP-BY that adds additional Summary to result-set.
• When SELECT query contains GROUP with CUBE | ROLLUP, it returns two result sets; one result set for query execution output and second for Summary.  
• The ouput is similar to writing two queries by combing using UNION-ALL, but SETS-Operators are more efficient to write.

Syntax:

GROUP BY expression[,..n] [WITH {CUBE | ROLLUP} ]

Examples :

Let us consider a M:M:M relationship. E.g, Employees:Projects:TeamLeader where

1. Each project have multiple employees, and each employee works in multiple project.
2. Each project is handled by multiple Team Leaders and each Team leader lead multiple projects.

A: CUBE:

CUBE: Appends summary report of all possible combinations of values in aggregate columns.

1. Find TOTAL and SUB-TOTAL for each Project, Employee (All combinations PROJECT: EMPLOYEE)

   Query	OUTPUT
   SELECT Project,  [Emp Name],   COUNT(*) [No of Employees] FROM Table1 GROUP BY  Project, [Emp Name] WITH CUBE = SELECT Project, [Emp Name], COUNT(*) FROM Table1 GROUP BY Project, [Emp Name] UNION ALL  Select NULL, NULL, Count(*) FROM Table1 UNION ALL Select NULL, [Emp Name], Count(*) FROM Table1; GROUP BY [Emp Name]

2. Find TOTAL and SUB-TOTAL for each Project, Employee, TeamLeader Groups.

   SELECT Project, [Emp Name], [TeamLeader], COUNT(*) AS [No of Times]
   FROM Table1
   GROUP BY Project, [Emp Name] , [TeamLeader] WITH CUBE
   
   OUTPUT
   
   

B: ROLL UP

• Similar to CUBE it appends summary report to resultset, but works with only single combination of values specified in function.
• It enables a SELECT statement to calculate multiple levels (hierarchical) of aggregate value (e.g. subtotals) across a specified group of dimensions. and whole-level of aggregate value(e.g., grand total.)

1. Find TOTAL and SUB-TOTAL no. of Employees in each Project ( ie., Single group Combination)

   SELECT Project, [Emp Name], COUNT(*) AS [No of Employees]
   FROM Table1
   GROUP BY Project, [Emp Name] WITH ROLLUP
   
   =
   
   Select Project, EmpName, Count(*) FROM Table1
   GROUP BY Project, EmpName
   UNION ALL
   Select NULL, NULL, Count(*) FROM Table1
   
   OUTPUT
   
   

ROLLUP simply summarizing data hierarchically whereas CUBE summarizing as whole.

5. GROUPING - Row indicator for CUBE | ROLLUPs

• It differentiates stored values (NULL or Not-NULL) from NULL values created by ROLLUP/CUBE NULL.
• It returns 1 if row is ROLLUP(/CUBE) have NULL-value otherwise 0.
• It identifies in which rows Total and Sub-totals.
• The main usage is to do decoration to ROLLUP/CUBE summery result.

Examples:

Example-1: Show what changes are made by GROUPING()verb

Without GROUPING()	With GROUPING()
SELECT Project, [Emp Name] FROM Table1 GROUP BY Project, [Emp Name] WITH ROLLUP	SELECT Project, GROUPING([Emp Name]) FROM Table1 GROUP BY Project, [Emp Name] WITH ROLLUP
OUTPUT: Project Emp Name Project1 e1 Project1 e2 Project1 e3 Project1 e4 Project1 NULL Project2 e1 Project2 e2 Project2 NULL Project3 e1 Project3 e4 Project3 NULL	OUTPUT: Project Emp Name Project1 0 Project1 0 Project1 0 Project1 0 Project1 1 Project2 0 Project2 0 Project2 1 Project3 0 Project3 0 Project3 1

Example-2: Decorating with GROUPING()

SELECT
        CASE WHEN GROUPING([Project])=1 
            THEN 'All Projects'
            ELSE [Project ] END,
       CASE WHEN GROUPING([Emp Name])=1 
            THEN 'Total Emps'
            ELSE [Emp Name] END AS [Employees],
       COUNT(*) AS [No of Employees]
FROM Table1
GROUP BY Project, [Emp Name] WITH ROLLUP

OUTPUT

Project	Emp Name	Employees
Project1	e1	1
Project1	e2	1
Project1	e3	1
Project1	e4	1
Project1	Total	4
Project2	e1	1
Project2	e2	1
Project2	Total Emps	2
Project3	e1	1
Project3	e4	1
Project3	Total Emps	2
All Projects	Total Emps	8