Multiple rows of data can be calculated using SQL's aggregate functions, which yield 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;

2. Using SUM()
SELECT SUM(Salary) FROM Employees;

3. Using AVG()
SELECT AVG(Salary) FROM 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

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

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

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

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.

HostForLIFEASP.NET SQL Server 2022 Hosting