SQL | Aggregate functions

SQL aggregate functions return a single value, calculated from values in a column. They are commonly used with the GROUP BY clause to group rows that have the same values in specified columns into summary rows, like "find the number of customers in each country".

Here's a brief overview of the commonly used aggregate functions:

COUNT(): Returns the number of items in a set.
```
SELECT COUNT(column_name)
FROM table_name;
```
SUM(): Returns the total sum of a numeric column.
```
SELECT SUM(column_name)
FROM table_name;
```
AVG(): Returns the average value of a numeric column.
```
SELECT AVG(column_name)
FROM table_name;
```
MIN(): Returns the smallest value in a set.
```
SELECT MIN(column_name)
FROM table_name;
```

MAX(): Returns the largest value in a set.

SELECT MAX(column_name)
FROM table_name;

GROUP_CONCAT() in MySQL / STRING_AGG() in SQL Server / LISTAGG() in Oracle: Returns a string result with the concatenated non-NULL values from a group.

-- MySQL
SELECT GROUP_CONCAT(column_name)
FROM table_name;

-- SQL Server
SELECT STRING_AGG(column_name, ',')
FROM table_name;

-- Oracle
SELECT LISTAGG(column_name, ',')
FROM table_name;

Using with GROUP BY:

When you use the GROUP BY clause, you can group rows that have the same values in specified columns into summary rows. Aggregate functions can then be applied on each group.

SELECT column1, COUNT(column2), SUM(column3)
FROM table_name
GROUP BY column1;

This example would group the results by column1, and for each group, it would provide the count of column2 values and the sum of column3 values.

Using with HAVING:

The HAVING clause lets you filter result sets based on aggregate calculations:

SELECT column1, COUNT(column2)
FROM table_name
GROUP BY column1
HAVING COUNT(column2) > 5;

This would only return groups where column2 has more than 5 items.

It's essential to understand that while the WHERE clause filters rows before they're grouped, the HAVING clause filters after rows have been grouped. This distinction is crucial when working with aggregate functions and grouped data.

SUM Function in SQL:

SELECT SUM(Salary) AS TotalSalary
FROM Employees;

AVG Function in SQL:

SELECT AVG(Salary) AS AverageSalary
FROM Employees;

COUNT Function in SQL:

SELECT COUNT(EmployeeID) AS EmployeeCount
FROM Employees;

MAX and MIN Functions in SQL:

SELECT MAX(Salary) AS MaxSalary
FROM Employees;

SELECT MIN(Salary) AS MinSalary
FROM Employees;

GROUP BY Clause with SQL Aggregate Functions:

SELECT DepartmentID, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentID;

HAVING Clause in SQL for Aggregate Conditions:

SELECT DepartmentID, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentID
HAVING AVG(Salary) > 50000;

SQL Aggregate Functions with DISTINCT:

SELECT COUNT(DISTINCT DepartmentID) AS UniqueDepartments
FROM Employees;

Combining Aggregate Functions in SQL:

SELECT MAX(Salary) AS MaxSalary, MIN(Salary) AS MinSalary, AVG(Salary) AS AvgSalary
FROM Employees;

Using Aggregate Functions with JOIN in SQL:

SELECT d.DepartmentName, AVG(e.Salary) AS AvgSalary
FROM Departments d
JOIN Employees e ON d.DepartmentID = e.DepartmentID
GROUP BY d.DepartmentName;

Nested Aggregate Functions in SQL:

SELECT AVG(MAX(Salary)) AS AvgMaxSalary
FROM Employees
GROUP BY DepartmentID;

NULL Values and Aggregate Functions in SQL:

SELECT AVG(Salary) AS AvgSalary
FROM Employees
WHERE Salary IS NOT NULL;

Window Functions vs. Aggregate Functions in SQL:

Aggregate Function:

SELECT DepartmentID, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentID;

Window Function:

SELECT DepartmentID, AVG(Salary) OVER (PARTITION BY DepartmentID) AS AvgSalary
FROM Employees;