| How to
use the GROUPING function? |
|
Execute the following
script in Query Editor to create "total" labels applying
the GROUPING function:
USE AdventureWorks;
SELECT TotalProduct=
CASE GROUPING(ProductModelID)
WHEN 0 THEN ''
WHEN 1 THEN 'Total'
ELSE ''
END,
TotalModel=
CASE GROUPING(p.Name)
WHEN 0 THEN ''
WHEN 1 THEN 'Total'
ELSE ''
END,
Model = isnull(convert(varchar,ProductModelID),''),
ProductName = isnull(p.Name,''),
OrderQuantity = SUM(OrderQty)
FROM Production.Product p
INNER JOIN Sales.SalesOrderDetail sod
ON p.ProductID = sod.ProductID
GROUP BY ProductModelID, p.Name
WITH CUBE
ORDER BY TotalModel, TotalProduct;
GO
|
| The Best SQL Server
2005 Training in the World |
|