In this example, we calculated the the customer wise total, Project wise total and Grant total.
First we create a below table, it is having the 4 colums. Here NoOfDocuments means, each user(DoaminID) worked document count as per customer project.
CREATE TABLE #tmpTestGrouping (
Customer VARCHAR(100),
Project VARCHAR(100),
DomainID VARCHAR(100),
[NoOfDocuments] INT
)
INSERT INTO #tmpTestGrouping
SELECT 'UHC', 'ACE - Adjudication','nsuresh',9 UNION ALL
SELECT 'UHC', 'ACE - Adjudication','rkbabu',8 UNION ALL
SELECT 'UHC', 'ACE - Adjudication','vijayans',5.5 UNION ALL
SELECT 'UHC', 'ACE - Adjudication','venkatramanp',7.5 UNION ALL
SELECT 'UHC', 'HN West','veeramainp',9.5 UNION ALL
SELECT 'UHC', 'HN West','jeyavania',2.5 UNION ALL
SELECT 'UMR', 'HN West','gpsuba',1.5 UNION ALL
SELECT 'UHC', 'UBH-PDM','kalai',30 UNION ALL
SELECT 'UHC', 'UBH-PDM','rajkumar',20 UNION ALL
SELECT 'UHC', 'UBH-PDM','deepu',11 UNION ALL
SELECT 'UHC', 'UBH-PDM','prakeerthvijayan',6.5
Select the Table now,
SELECT * FROM #tmpTestGrouping
Now we gets the all results of Grouping with the DocumentsTotal.
SELECT
GROUPING(Customer) GroupingCustomer,
GROUPING(Project) GroupingProject,
GROUPING(DomainID) GroupingDoaminID,
Customer,
[Project],
DomainID,
SUM ([NoOfDocuments]) AS [NoOfDocuments]
FROM #tmpTestGrouping
GROUP BY Customer,[Project],DomainID WITH cube
In the above fig, GroupingCustomer, GroupingProject, GroupingDoaminID values only having 0 or 1 that is a boolean. If its 1 means that columns grouped as per the aggregate function which is given by us.
No we need to get the Total, based on the below condition we will get the result as per the columns or clustered columns or the Grand total.
SELECT
CASE WHEN GROUPING(Customer) = 1 AND GROUPING([Project]) = 1 AND
GROUPING(DomainID) = 1 THEN 'Grand Total'
WHEN GROUPING(Customer) = 0 AND GROUPING([Project]) = 0 AND
GROUPING(DomainID) = 1 THEN ''
WHEN GROUPING([Project]) = 1 AND GROUPING(DomainID) = 1 THEN ''
ELSE Customer END AS Customer,
CASE WHEN GROUPING(Customer) = 0 AND GROUPING([Project]) = 1 AND
GROUPING(DomainID) = 1 THEN 'Customer Total'
WHEN GROUPING(DomainID) = 1 AND GROUPING(Customer) = 0 THEN ''
WHEN GROUPING(Customer) = 1 THEN '' ELSE
CAST([Project] AS VARCHAR) END AS [Project],
CASE WHEN GROUPING(Customer) = 1 AND GROUPING([Project]) = 1 AND
GROUPING(DomainID) = 1 THEN ''
WHEN GROUPING(Customer) = 0 AND GROUPING([Project]) = 0 AND
GROUPING(DomainID) = 1 THEN 'Project Total'
WHEN GROUPING(Customer) = 0 AND GROUPING([Project]) = 1 AND
GROUPING(DomainID) = 1 THEN ''
ELSE DomainID END AS DomainID,SUM ([NoOfDocuments]) AS [NoOfDocuments]
FROM
#tmpTestGrouping
GROUP BY Customer,[Project],DomainID WITH ROLLUP
The output is like below,
This helps to calculate the totals instead of calculate from our application.
01:35 |
Category:
SQL SERVER
|
0
comments
Comments (0)