The SQL CHECKSUM_AGG is one of the Aggregate Function, which is used to return the checksum of the values in a Group. Or we can say, CHECKSUM_AGG will detect the changes in a column. For this CHECKSUM_AGG demo, We are going to use the below-shown data
SQL CHECKSUM_AGG Example
The following example uses the CHECKSUM_AGG Function to detect the changes in Employee Id, Dept Id, and Manager Id columns. Please refer to the list of SQL Aggregate Functions from the SQL Programming page.
SELECT CHECKSUM_AGG(CAST([EmployeeID] AS INT)) AS [Emp ID]
,CHECKSUM_AGG(CAST(DeptID AS INT)) AS [Department ID]
,CHECKSUM_AGG(CAST([ManagerID] AS INT)) AS [Manager ID]
FROM [MyEmployees]
SQL CHECKSUM_AGG with Distinct Keyword
In this SQL SELECT query, we are going to find the checksum value of the distinct records using the CHECKSUM_AGG function. The SQL DISTINCT keyword is used to remove duplicates from the specified column Name in SQL Server.
SELECT CHECKSUM_AGG(CAST([EmployeeID] AS INT)) AS [Emp ID]
,CHECKSUM_AGG(DISTINCT CAST(DeptID AS INT)) AS [Unique Department ID]
,CHECKSUM_AGG(DISTINCT CAST([ManagerID] AS INT)) AS [Unique Manager ID]
FROM [MyEmployees]
SQL CHECKSUM_AGG with Group By
This Aggregate Function example will show you how to use the CHECKSUM_AGG function along with the GROUP BY Clause. Please refer to the SQL SUM and SQL AVG functions to understand the grouping techniques and calculate the total and average. Refer to the GROUP BY Clause in SQL for grouping columns.
Here, we used the SQL CAST function to convert the ID values to integers. Similarly, we can use the SQL TRY_CAST method.
SELECT Occupation
,CHECKSUM_AGG(CAST([EmployeeID] AS INT)) AS [Emp ID]
,CHECKSUM_AGG(DISTINCT CAST(DeptID AS INT)) AS [Unique Department ID]
,CHECKSUM_AGG(DISTINCT CAST([ManagerID] AS INT)) AS [Unique Manager ID]
FROM [MyEmployees]
GROUP BY Occupation
