Aggregate Functions in SQL Server

COUNT(): count rows

COUNT() determines how many records exist in a dataset or how many non-NULL values a specific column contains. It's one of the most frequently used aggregates for quick overviews. Here is what it does:

• COUNT(*) counts every row, including those with NULL values.
• COUNT(column_name) counts only rows where that column is not NULL.

Example

SELECT  
    DepartmentID,  
    COUNT(*) AS TotalEmployees, 
    COUNT(Email) AS EmployeesWithEmail 
FROM Employees 
GROUP BY DepartmentID; 

This aggregate function in SQL with example compares total employees against those who've provided an email address, illustrating the difference between counting all rows and counting specific column values.

SUM(): total of values

SUM() adds up all numeric values in a column. It's the go-to function for calculating total revenue, costs, or any accumulated metric.

Example

SELECT  
    DepartmentID,  
    SUM(Salary) AS TotalSalary 
FROM Employees 
GROUP BY DepartmentID; 
							

This code snippet shows how to calculate the total salary expense for each department, a common query in financial and HR reporting.

AVG(): average value

AVG() computes the mean of numeric values, helping identify performance trends or typical results across a dataset.

Example

SELECT  
    DepartmentID,  
    AVG(Salary) AS AverageSalary 
FROM Employees 
GROUP BY DepartmentID; 
							

Averages like this are useful for comparing compensation levels, evaluating team performance, or analyzing grades and survey scores.

MIN() and MAX(): minimum and maximum

MIN() and MAX() return the lowest and highest values in a dataset, often used to define boundaries or highlight extremes.

Example

SELECT  
    MIN(HireDate) AS FirstHire,  
    MAX(HireDate) AS LatestHire 
FROM Employees; 
							

This query identifies the earliest and most recent hires, useful for understanding workforce growth or data ranges over time.

STRING_AGG(): concatenate strings

Introduced in SQL Server 2017, STRING_AGG() allows you to combine multiple string values into one, separated by a chosen delimiter. It replaces the verbose XML-based concatenation patterns developers once relied on.

Example

SELECT  
    TeamID,  
    STRING_AGG(EmployeeName, ', ') AS TeamMembers 
FROM Employees 
GROUP BY TeamID; 
							

This query produces a clean list of all employees per team. It's widely used to generate readable lists of tags, categories, or grouped names in reports.

APPROX_COUNT_DISTINCT(): fast distinct count

Available in SQL Server 2019 and later, APPROX_COUNT_DISTINCT() delivers a fast, approximate count of unique values. It's designed for large datasets where speed is more important than perfect precision.

Example

SELECT  
    APPROX_COUNT_DISTINCT(CustomerID) AS EstimatedUniqueCustomers 
FROM Sales; 
							

This function can dramatically improve performance when working with billions of records: ideal for data warehousing, telemetry, and real-time analytics where slight variance is acceptable.

STDEV(), STDEVP(), VAR(), VARP(): statistical functions

These functions calculate statistical measures that describe the variability or spread in numeric data. Here is what they do:

• STDEV() and VAR() calculate standard deviation and variance for a sample.
• STDEVP() and VARP() calculate them for the entire population.

Example

SELECT  
    STDEV(SaleAmount) AS SalesDeviation, 
    VAR(SaleAmount) AS SalesVariance 
FROM Sales; 
							

They are essential in finance, manufacturing, and research environments: where understanding fluctuations, consistency, or volatility is critical to decision-making.

CHECKSUM_AGG(): detecting data changes

CHECKSUM_AGG() produces a single checksum value that represents the combined contents of a column or set of columns. It's a lightweight method to verify whether data has changed between two points in time.

Example

SELECT  
    DepartmentID,  
    CHECKSUM_AGG(BINARY_CHECKSUM(*)) AS DataChecksum 
FROM Employees 
GROUP BY DepartmentID; 
							

By comparing checksum outputs, you can detect modifications without row-by-row comparisons, an efficient approach for ETL pipelines, replication monitoring, or integrity audits.

GROUPING() and GROUPING_ID()

When using ROLLUP or CUBE to produce hierarchical totals, GROUPING() and GROUPING_ID() identify whether a row represents a detailed record, a subtotal, or a grand total.

Example

SELECT  
    Department,  
    YEAR(HireDate) AS HireYear, 
    COUNT(*) AS EmployeeCount, 
    GROUPING(Department) AS IsDepartmentTotal, 
    GROUPING_ID(Department, YEAR(HireDate)) AS GroupLevel 
FROM Employees 
GROUP BY ROLLUP (Department, YEAR(HireDate)); 
							

These functions help distinguish between grouping levels in reports: crucial for creating dynamic summaries and drill-down dashboards that reflect real business hierarchies.

GROUP BY clause

The GROUP BY clause organizes rows that share the same values in one or more columns, allowing aggregate functions to compute results for each group instead of the entire dataset.

Example: Average salary per department

SELECT  
    DepartmentID,  
    AVG(Salary) AS AverageSalary 
FROM Employees 
GROUP BY DepartmentID; 
							

This query returns the average salary for each department. Without GROUP BY, the AVG() function would calculate a single average across all employees rather than per department.

GROUP BY can also be combined with multiple columns: for example, grouping by both department and job title to compare averages within subcategories.

HAVING clause

While WHERE filters rows before SQL aggregation, the HAVING clause filters after aggregates have been calculated. It's used to apply conditions to grouped results.

Example: Departments with more than 10 employees

SELECT  
    DepartmentID,  
    COUNT(*) AS EmployeeCount 
FROM Employees 
GROUP BY DepartmentID 
HAVING COUNT(*) > 10; 
							

This query lists only departments that have more than 10 employees, excluding smaller ones from the final result.

In practice, GROUP BY and HAVING work together to produce precise summaries. GROUP BY defines how the data is segmented, while HAVING refines which aggregated groups appear in the final output.

How SQL Server supports CLR aggregates

A CLR-based aggregate receives rows of data from SQL Server, processes each value through Accumulate(), merges partial results via Merge(), and returns a final value using Terminate(). This design enables complex computations—such as medians, geometric means, or domain-specific metrics—that built-in SQL functions can't easily express or optimize.

When and why to use them

CLR-based aggregates shine in scenarios where built-in functions reach their limits and more specialized logic is needed. They are particularly useful when:

• Custom calculations require precision or algorithms unavailable in T-SQL.
• Aggregation logic must stay consistent across multiple databases or projects.
• Compute-heavy or iterative operations need the efficiency of compiled .NET code.
• Complex formulas should be encapsulated behind a simple, reusable aggregate call.

In short, use them when the goal is to extend SQL Server's analytical range, not to re-implement existing functionality. They should solve a clear limitation or performance bottleneck, not replace simpler built-in functions.

Requirements

Creating a CLR-based aggregate in SQL Server requires both .NET code and server configuration. The setup involves three key parts:

1. Enable CLR integration. CLR must be explicitly enabled at the server level before deploying any assemblies.

 EXEC sp_configure 'clr enabled', 1; 
RECONFIGURE; 
							

2. Develop the .NET assembly. Implement the aggregate as a struct (or class) in C# or VB.NET using the [SqlUserDefinedAggregate] attribute, and define the required methods, Init(), Accumulate(), Merge(), and Terminate().

Example structure

 [Serializable] 
[SqlUserDefinedAggregate(Format = Format.UserDefined, MaxByteSize = -1)] 
public struct Median 
{ 
    public void Init() { } 
    public void Accumulate(SqlDouble value) { } 
    public void Merge(Median other) { } 
    public SqlDouble Terminate() => new SqlDouble(); 
} 
							

3. Register the assembly in SQL Server. After compilation, load the DLL and create the aggregate definition.

Example

 CREATE ASSEMBLY CustomAggregates 
FROM 'C:\Path\CustomAggregates.dll' 
WITH PERMISSION_SET = SAFE; 
 
CREATE AGGREGATE dbo.Median (@Value FLOAT) 
RETURNS FLOAT 
EXTERNAL NAME CustomAggregates.[YourNamespace.Median]; 
							

Once registered, the CLR aggregate can be used like any native function:

SELECT DepartmentID, dbo.Median(Salary) AS MedianSalary 
FROM Employees 
GROUP BY DepartmentID; 
							

Best practice: keep assemblies small, mark them SAFE, and use them only when they add measurable value beyond built-in SQL functions.