In this Example, When the time of Aggregate Functions calculation, sometimes we need to remove the duplicates from our count, its may be an SUM or COUNT. See the below example for quick understand
CREATE TABLE #DistinctAggregate(id INT IDENTITY(1,1),EmpID INT,DocumentNo INT)
INSERT INTO #DistinctAggregate(EmpID,DocumentNo) VALUES(1,14)
INSERT INTO #DistinctAggregate(EmpID,DocumentNo) VALUES(1,13)
INSERT INTO #DistinctAggregate(EmpID,DocumentNo) VALUES(1,14)
INSERT INTO #DistinctAggregate(EmpID,DocumentNo) VALUES(2,1000)
INSERT INTO #DistinctAggregate(EmpID,DocumentNo) VALUES(2,1000)
SELECT * FROM #DistinctAggregate
SELECT EmpID,COUNT(DISTINCT DocumentNo) TotalDocuments FROM #DistinctAggregate GROUP BY EmpID
The result is like below
The Same Table , I need to calculate the employees whoes are worked in the Document no 14 then use the below query.
SELECT EmpID,COUNT(DISTINCT Case when DocumentNo = 14 THEN DocumentNo else NULL END) TotalDocuments from #DistinctAggregate
GROUP BY EmpID
The resul is like below
This is just an example. Based on our need we will make use of it logic.
05:01 |
Category:
SQL SERVER
|
0
comments
Comments (0)