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 :
Microsoft SQL Server Training Online Learning Classes Advance GROUPING COMPUTE ROLLUP CUBE PARTITION NTILE



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:

    Microsoft SQL Server Training Online Learning Classes Advance GROUPING COMPUTE ROLLUP CUBE PARTITION NTILE
  2. It is similar to SELECT SUM(salary), AVG(salary). But not followed by Query res
  3. 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
  4. 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

4 comments: