Aggregate Functions

In SQL, aggregate functions are special functions that operate on a set of values from one or more rows in a table and return a single value as a result. These functions are used to perform calculations on groups of rows or an entire table and are commonly used with the SELECT statement in combination with the GROUP BY clause.

There are several standard aggregate functions in SQL, including:

COUNT: The COUNT function returns the number of rows that match the specified criteria. It counts the total number of non-null values in a column or the number of rows in a result set.

Example:

SELECT COUNT(*) AS TotalEmployees
FROM Employees;

This query will return the total number of employees in the "Employees" table.

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

Example:

SELECT SUM(Quantity) AS TotalQuantity
FROM OrderDetails;

This query will return the total quantity of products ordered from the "OrderDetails" table.

AVG: The AVG function calculates the average value of a numeric column.

Example:

SELECT AVG(Price) AS AveragePrice
FROM Products;

This query will return the average price of products in the "Products" table.

MAX: The MAX function retrieves the maximum value from a column.

Example:

This query will return the highest price among all products in the "Products" table.

MIN: The MIN function retrieves the minimum value from a column.

Example:

This query will return the lowest price among all products in the "Products" table.

Aggregate functions can also be used in combination with the GROUP BY clause to perform calculations on groups of rows based on common values in a column.

Example:

In this example, the query calculates the average salary for each department in the "Employees" table using the GROUP BY clause.

Aggregate functions are powerful tools for analyzing and summarizing data in SQL queries. They allow you to perform calculations on large datasets efficiently and obtain valuable insights from the data in your database.