Tuesday, September 30, 2014

0 T-SQL Data Aggregation

This is note that I test various Data Aggregation functions in SQL Server.

  • GROUP
  • ROLLUP
  • CUBE
  • GROUPING SET
  • GROUPING
  • GROUPING_ID
  • PIVOT

In the simple GROUP, it returns 27 rows. ROLLUP provides some combination of the sum, it return 37 rows. CUBE provides all different combination of SUM, it returns 126 rows.

GROUPING SET gives ability to choice what returns in the CUBE we want to see.

GROUPING provides additional column to  indicate whether that particular column is summary data or not.

GROUPING_ID, similar to GROUPING but give us the ID to identify what kind of summary for that row.

PIVOT functions convert row data in to column.

UNPIVOT functions convert column data back to row. However, if there are 2 row data before, it can not distinguish but only convert one row data back.

Product Year Sales
Item 1 2014 100
Item 2 2014 200
Item 2 2013 250
Item 3 2014 50
Item 3 2014 300

Above table will become Pivot as below

  2013 2014
Item 1 0 100
Item 2 250 200
Item 3 0 350

If we UNPIVOT  it, it will change to below

Product Year Sales
Item 1 2014 100
Item 2 2014 200
Item 2 2013 250
Item 3 2014 350

SQL server can not distinguish there were 2 rows of Item 3 in 2014, so it combine both into one row.

Create the test View

Below is the test View. I didn’t come up with the view myself, it is coming from Suherman, STP

USE [AdventureWorks2014]
GO
Create view Sales.vSalesByCategory
AS 
select pc.Name Category,
    ps.Name SubCategory,
    p.Name Product,
    soh.TotalDue 
from sales.SalesOrderHeader soh
inner join Sales.SalesOrderDetail sod on soh.SalesOrderID =sod.SalesOrderDetailID
inner join Production.Product p on sod.productID = p.ProductID
inner join Production.ProductSubcategory ps on p.ProductSubcategoryID= ps.ProductSubcategoryID
inner join Production.ProductCategory pc on ps.ProductCategoryID =pc.ProductCategoryID

GROUP

select Category,SubCategory,Product,SUM (TotalDue) 
from Sales.vSalesByCategory
Where Category='Clothing'
GROUP BY Category,SubCategory,Product
ORDER BY  Category,SubCategory,Product

image

ROLLUP

--- ROLLUP
select Category,SubCategory,Product,SUM (TotalDue) 
from Sales.vSalesByCategory
Where Category='Clothing'
GROUP BY Category,SubCategory,Product
WITH ROLLUP
ORDER BY  Category,SubCategory,Product

image

CUBE

--- CUBE
select Category,SubCategory,Product,SUM (TotalDue) 
from Sales.vSalesByCategory
Where Category='Clothing'
GROUP BY Category,SubCategory,Product
WITH CUBE
ORDER BY  Category,SubCategory,Product

image

GROUPING SET

select Category,SubCategory,Product,SUM (TotalDue) 
from Sales.vSalesByCategory
Where Category='Clothing'
GROUP BY GROUPING SETS ( (Category,SubCategory) , (Category), (Product) )
ORDER BY  Category,SubCategory,Product

image

GROUPING

--- GROUPING 
select Category,
        GROUPING(Category) AS 'CategoryTotal',
        SubCategory,
        GROUPING(SubCategory) AS 'SubCategoryTotal',
        Product,
        GROUPING(Product) AS 'ProductTotal',
        SUM (TotalDue) 
from Sales.vSalesByCategory
Where Category='Clothing'
GROUP BY Category,SubCategory,Product
WITH CUBE
ORDER BY  Category,SubCategory,Product

image

GROUPING_ID

--- GROUPING_ID
select  GROUPING_ID(Category, SubCategory,Product ) AS TotalBitMap, 
        SubCategory,
        Product,
        SUM (TotalDue) 
from Sales.vSalesByCategory
Where Category='Clothing'
GROUP BY Category,SubCategory,Product
WITH CUBE
ORDER BY  Category,SubCategory,Product
 
--1: Product
--2. Subcategory
--3. SubCategory, Product
--4. Category
--5. Category, Product
--6. Category,SubCategory
--7. Category,SubCategory,Product

image

PIVOT

WITH Salesdata( TotalSold,OrderYear,TerritoryName)
AS (
    SELECT SUM ( SOH.TotalDue ) AS 'TotalSold',
        YEAR(SOH.OrderDate) AS 'OrderYear',
        ST.Name AS 'TerritoryName'
    FROM Sales.SalesOrderHeader SOH inner join Sales.SalesTerritory ST on
        SOH.TerritoryID = ST.TerritoryID
    GROUP BY YEAR(soh.OrderDate), ST.Name
)
-- SELECT * from Salesdata;
--- PIVOT
 
SELECT * from Salesdata
    PIVOT (SUM (TotalSold)
        FOR OrderYear IN ([2011],[2012],[2013],[2014])
    ) AS pvt;

Before Pivot

image

After Pivot

image

Reference

http://social.technet.microsoft.com/wiki/contents/articles/23005.introducing-data-rollup-id-id.aspx

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates