T-SQL Aggregation Functions Cheatsheet

Aggregation functions play a crucial role in SQL queries, allowing us to summarize and analyze data within a table. Here, we will provide a handy cheatsheet for T-SQL aggregation functions, including examples and explanations of each function. We will demonstrate the usage of these functions using a sample table called “Employees” with columns such as EmployeeID, FirstName, LastName, Department, and Salary. Whether you’re new to SQL or looking to refresh your knowledge, this guide will serve as a valuable reference for mastering T-SQL aggregation functions.

Sample Table: Employees

EmployeeIDFirstNameLastNameDepartmentSalary
1JohnDoeHR5000
2JaneSmithSales6000
3MarkJohnsonHR4500
4EmilyDavisFinance7000
5MichaelBrownFinance5500

Now, let’s dive into the T-SQL aggregation functions.

1. COUNT Function:

The COUNT function returns the number of rows that match a specific condition or the total number of rows in a table.

SELECT COUNT(*) AS TotalRows FROM Employees;
-- Output: TotalRows = 5

SELECT COUNT(DISTINCT Department) AS UniqueDepartments FROM Employees;
-- Output: UniqueDepartments = 3

2. SUM Function:

The SUM function calculates the sum of values in a numeric column.

SELECT SUM(Salary) AS TotalSalary FROM Employees;
-- Output: TotalSalary = 28000

3. AVG Function:

The AVG function calculates the average of values in a numeric column.

SELECT AVG(Salary) AS AverageSalary FROM Employees;
-- Output: AverageSalary = 5600

4. MIN Function:

The MIN function retrieves the minimum value from a column.

SELECT MIN(Salary) AS MinimumSalary FROM Employees;
-- Output: MinimumSalary = 4500

5. MAX Function:

The MAX function retrieves the maximum value from a column.

SELECT MAX(Salary) AS MaximumSalary FROM Employees;
-- Output: MaximumSalary = 7000

6. GROUP BY Clause:

The GROUP BY clause groups rows based on one or more columns and allows us to perform aggregate functions on each group.

SELECT Department, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department;

Output:

DepartmentEmployeeCount
HR2
Sales1
Finance2

7. HAVING Clause:

The HAVING clause filters groups based on conditions specified in aggregate functions.

SELECT Department, AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > 5000;

Output:

DepartmentAverageSalary
Sales6000
Finance6250

8. DISTINCT Keyword:

The DISTINCT keyword removes duplicate values from a column or combination of columns.

SELECT DISTINCT Department FROM Employees;

Output:

Department
HR
Sales
Finance

9. GROUPING SETS:

The GROUPING SETS clause allows us to specify multiple groupings in a single query.

SELECT Department, FirstName, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY GROUPING SETS ((Department), (FirstName));

Output:

DepartmentFirstNameEmployeeCount
HRNULL2
NULLJohn1
NULLJane1
NULLMark1
NULLEmily1
NULLMichael1

10. ROLLUP:

The ROLLUP operator generates subtotals and grand totals for multiple levels of grouping.

SELECT Department, FirstName, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY ROLLUP (Department, FirstName);

Output:

DepartmentFirstNameEmployeeCount
HRNULL2
HRJohn1
NULLJane1
NULLMark1
FinanceNULL2
NULLEmily1
NULLMichael1


Mastering T-SQL aggregation functions is essential for analyzing and summarizing data in SQL queries. This cheatsheet has provided you with a comprehensive guide, complete with code examples and outputs, for the most commonly used aggregation functions in T-SQL. Use this reference to enhance your SQL skills and efficiently perform calculations and analysis on your data.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top