Understanding Aggregate Functions in SQL

Aggregate functions in SQL are used to perform calculations on multiple rows of data and return a single result.

Common Aggregate Functions

COUNT(): Returns the number of rows.
SUM(): Returns the total sum of a numeric column.
AVG(): Returns the average value of a numeric column.
MIN(): Returns the minimum value.
MAX(): Returns the maximum value.

Example Usage of Aggregate Functions

1. Using COUNT()

SELECT COUNT(*) FROM Employees;

Output. 50 (Total number of employees)

2. Using SUM()

SELECT SUM(Salary) FROM Employees;

Output. 5,000,000 (Total salary of all employees)

3. Using AVG()

SELECT AVG(Salary) FROM Employees;

Output. 100,000 (Average salary of employees)

4. Using MIN() and MAX()

SELECT MIN(Salary) FROM Employees; 

SELECT MAX(Salary) FROM Employees;

Output

MIN(Salary): 50,000 (Lowest salary)
MAX(Salary): 200,000 (Highest salary)

Using GROUP BY with Aggregate Functions

GROUP BY is often used with aggregate functions to group results by one or more columns.

SELECT 
 Department, AVG(Salary) 
FROM Employees 
GROUP BY Department;

Output

Department	AVG(Salary)
IT	120,000
HR	80,000
Finance	110,000

Using HAVING with Aggregate Functions

HAVING is used to filter results after aggregation.

SELECT 
 Department, COUNT(*) 
FROM Employees 
GROUP BY Department 
 HAVING COUNT(*) > 10;

Output

Department	COUNT(*)
IT	15
Finance	12

Advanced Use of Aggregate Functions

Aggregate functions in SQL can be used in advanced ways to solve complex data analysis problems efficiently.

1. Using Aggregate Functions with CASE

SELECT Department,
       SUM(   CASE
                  WHEN Gender = 'Male' THEN
                      1
                  ELSE
                      0
              END
          ) AS Male_Count,
       SUM(   CASE
                  WHEN Gender = 'Female' THEN
                      1
                  ELSE
                      0
              END
          ) AS Female_Count
FROM Employees
GROUP BY Department;

Output

Department	Male_Count	Female_Count
IT	10	5
HR	3	8

2. Using Aggregate Functions with DISTINCT

SELECT COUNT(DISTINCT Department) AS Total_Departments FROM Employees;

Output. 5 (Total distinct departments)

3. Using Aggregate Functions with PARTITION BY

PARTITION BY allows applying aggregate functions without collapsing rows.

SELECT EmployeeID,
       Name,
       Department,
       Salary,
       AVG(Salary) OVER (PARTITION BY Department) AS Avg_Department_Salary
FROM Employees;

Output

EmployeeID	Name	Department	Salary	Avg_Department_Salary
1	John	IT	120000	110000
2	Sarah	IT	100000	110000

4. Using Aggregate Functions with HAVING for Filtering

SELECT Department,
       COUNT(*) AS Employee_Count
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 10;

Output. Departments have more than 10 employees.

Advantages of Advanced Aggregate Functions

Allows detailed data analysis with conditions.
Enhances reporting and business intelligence capabilities.
Reduces query complexity using built-in SQL functions.
Helps in summarizing data.
Improves query efficiency by reducing result set size.
Facilitates data analysis and reporting.

These advanced aggregate functions help in efficient query design and deeper data insights.