Aggregate functions in MSSQL

In Microsoft SQL Server (MSSQL), aggregate functions are used to perform calculations on sets of values and return a single result. Here are some commonly used aggregate functions in MSSQL, along with examples:

1. SUM(): Calculates the sum of values in a column.

SELECT SUM(SalesAmount) AS TotalSales

FROM Sales;

2. AVG(): Calculates the average of values in a column.

SELECT AVG(Quantity) AS AvgQuantity

FROM OrderDetails;

3. COUNT(): Counts the number of rows in a result set or the number of non-null values in a column.

SELECT COUNT(*) AS TotalOrders

FROM Orders;

SELECT COUNT(ProductID) AS TotalProducts

FROM Products;

4. MIN(): Returns the smallest value in a column.

SELECT MIN(OrderDate) AS FirstOrderDate

FROM Orders;

5. MAX(): Returns the largest value in a column.

SELECT MAX(OrderDate) AS LastOrderDate

FROM Orders;

6. GROUP BY: Used with aggregate functions to group the result set by one or more columns.

SELECT CategoryID, AVG(UnitPrice) AS AvgPrice

FROM Products

GROUP BY CategoryID;

7. HAVING: Filters groups based on aggregate values.

SELECT CustomerID, SUM(OrderAmount) AS TotalOrders

FROM Orders

GROUP BY CustomerID

HAVING SUM(OrderAmount) > 1000;

These are just a few examples of aggregate functions in MSSQL. They allow you to perform various calculations and summaries on your data.