Aggregate Functions (T-SQL)

An aggregate function performs a calculation on a set of values, and returns a single value. Except for COUNT(*), aggregate functions ignore null values. Aggregate functions are often used with the GROUP BY clause of the SELECT statement.

Common Aggregate Functions

COUNT(): Returns the number of rows that match a specified criterion.
SUM(): Returns the total sum of a numeric column.
AVG(): Returns the average value of a numeric column.
MIN(): Returns the smallest value in a set.
MAX(): Returns the largest value in a set.

Example Usage

Here is an example of how to use aggregate functions in a SQL query:

SELECT COUNT(*) AS TotalOrders, SUM(TotalAmount) AS TotalSales
FROM Orders;

This query counts the total number of orders and sums the total sales amount from the Orders table.

Using GROUP BY with Aggregate Functions

Aggregate functions can be used with the GROUP BY clause to group rows that have the same values in specified columns into summary rows.

SELECT CustomerID, COUNT(*) AS OrderCount, SUM(TotalAmount) AS TotalSpent
FROM Orders
GROUP BY CustomerID;

This query groups the orders by CustomerID and returns the number of orders and total amount spent by each customer.

HAVING Clause

The HAVING clause is used to filter records that work on summarized GROUP BY results. It is similar to the WHERE clause but is used for aggregate functions.

SELECT CustomerID, COUNT(*) AS OrderCount
FROM Orders
GROUP BY CustomerID
HAVING COUNT(*) > 5;

This query returns customers who have placed more than 5 orders.

Conclusion

Aggregate functions are powerful tools in T-SQL that allow you to perform calculations on sets of data. They are commonly used in reporting and data analysis.