Aggregate functions in SQL Server perform a calculation on a set of values and return a single value. They are often used with the GROUP BY clause to group rows that share a common attribute. Here are the main aggregate functions in SQL Server:

1. COUNT

The COUNT function returns the number of rows in a set. There are two main variations:

• COUNT(*) counts all rows, including those with NULL values.
• COUNT(column_name) counts only non-NULL values in the specified column.

Examples:

-- Counts all rows in the table
SELECT COUNT(*) FROM Employees;

-- Counts only rows where Salary is not NULL
SELECT COUNT(Salary) FROM Employees;

2. SUM

The SUM function returns the total sum of a numeric column, ignoring NULL values.

Example:

SELECT SUM(Salary) FROM Employees;

3. AVG

The AVG function returns the average value of a numeric column, ignoring NULL values.

Example:

SELECT AVG(Salary) FROM Employees;

4. MIN

The MIN function returns the smallest value in a set. It can be used with numeric, string, or date columns.

Example:

SELECT MIN(Salary) FROM Employees;
SELECT MIN(JoinDate) FROM Employees;

5. MAX

The MAX the function returns the largest value in a set, Like MIN it can be used with numeric, string, or date columns.

Example:

SELECT MAX(Salary) FROM Employees;
SELECT MAX(JoinDate) FROM Employees;

6. STDEV and STDEVP

The STDEV the function calculates the statistical standard deviation of all values in the specified column (sample standard deviation), ignoring NULL values. STDEVP calculates the population standard deviation.

Examples:

SELECT STDEV(Salary) FROM Employees;
SELECT STDEVP(Salary) FROM Employees;

7. VAR and VARP

The VAR the function calculates the variance of all values in the specified column (sample variance), ignoring NULL values. VARP calculates the population variance.

Examples:

SELECT VAR(Salary) FROM Employees;
SELECT VARP(Salary) FROM Employees;

Using Aggregate Functions with GROUP BY

Aggregate functions are often used with the GROUP BY clause to group rows that share a common attribute and perform calculations on each group.

Example:

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

Using Aggregate Functions with HAVING

The HAVING clause is used to filter groups based on aggregate calculations. It is similar to the WHERE clause but is used with GROUP BY.

Example:

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

Handling NULL Values

Aggregate functions generally ignore NULL values except for COUNT(*), which includes NULL values.

Example:

SELECT 
COUNT(*) AS TotalEmployees, 
COUNT(Salary) AS EmployeesWithSalary 
FROM Employees;

Understanding and using these aggregate functions effectively can greatly enhance your ability to perform data analysis and reporting in SQL Server.