Aggregate functions

Aggregate functions in SQL perform calculations on a set of values and return a single value. These functions are commonly used with the GROUP BY clause, but they can also be used on their own for summarizing data across all rows.

Common Aggregate Functions:

1. COUNT(): Counts the number of rows or non-NULL values in a column.
2. SUM(): Adds up the values in a numeric column.
3. AVG(): Calculates the average of the values in a numeric column.
4. MIN(): Finds the minimum value in a column.
5. MAX(): Finds the maximum value in a column.

1. COUNT() Function

The COUNT() function returns the number of rows in a table, or the number of non-NULL values in a specific column.

Syntax:

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

Example:

Count the number of employees in the Employees table.

SELECT COUNT(*) AS EmployeeCount
FROM Employees;

Result:

If you want to count only non-NULL values in a specific column:

SELECT COUNT(DepartmentID) AS DepartmentCount
FROM Employees;

2. SUM() Function

The SUM() function adds up the values in a numeric column.Syntax:

Syntax:

SELECT SUM(column_name)
FROM table_name
WHERE condition;

Example:

Find the total sales amount from the Sales table.

SELECT SUM(TotalAmount) AS TotalSales
FROM Sales;

Result:

3. AVG() Function

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

Syntax:

SELECT AVG(column_name)
FROM table_name
WHERE condition;

Example:

Calculate the average salary of employees.

SELECT AVG(Salary) AS AverageSalary
FROM Employees;

Result:

4. MIN() Function

The MIN() function returns the smallest value in a column.

Syntax:

SELECT MIN(column_name)
FROM table_name
WHERE condition;

Example:

Find the lowest salary in the Employees table.

SELECT MIN(Salary) AS MinSalary
FROM Employees;

Result:

5. MAX() Function

The MAX() function returns the largest value in a column.

Syntax:

SELECT MAX(column_name)
FROM table_name
WHERE condition;

Example:

Find the highest salary in the Employees table.

SELECT MAX(Salary) AS MaxSalary
FROM Employees;

Result: