Aggregate Functions

⇒Aggregate Functions: count,min,sum,max,avg Here’s the SQL clean code version of the aggregate functions and queries explained, using the CompanyDB database:

1. Count non-null SSNs

   SELECT COUNT(SSN) AS NonNullSSNs
   FROM Employees
   WHERE SSN IS NOT NULL;
   
   

2. Count all rows

   SELECT COUNT(*) AS TotalRows
   FROM Employees;
   
   

3. Count non-null EmployeeName values (assuming EmployeeName replaces Fname)

   SELECT COUNT(EmployeeName) AS NonNullNames
   FROM Employees;
   
   

4. Average salary of employees

   SELECT AVG(Salary) AS AvgSalary
   FROM Employees;
   
   

5. Count of salaries with SSN grouped

   SELECT COUNT(Salary) AS SalaryCount, SSN
   FROM Employees
   GROUP BY SSN;
   
   SELECT COUNT(Salary) AS SalaryCount, SSN
   FROM Employees, Departments
   WHERE SSN = '123-45-6789'
   GROUP BY SSN;
   

6. Minimum salary grouped by department

   SELECT MIN(Salary) AS MinSalary, DepartmentID
   FROM Employees
   GROUP BY DepartmentID;
   
   

7. Count of employees by gender

   SELECT COUNT(SSN) AS GenderCount, Gender
   FROM Employees
   GROUP BY Gender;
   

8. Count of employees by gender in departments 10 or 20

   SELECT COUNT(SSN) AS GenderCount, Gender
   FROM Employees
   WHERE DepartmentID IN (10, 20)
   GROUP BY Gender;
   
   

9. Count of employees in departments where the address starts with 'a'

   SELECT COUNT(SSN) AS EmployeeCount, DepartmentID
   FROM Employees
   WHERE Address LIKE 'a%'
   GROUP BY DepartmentID;
   

Using HAVING to Filter Groups

1. Sum of salary grouped by gender with filtering

   SELECT SUM(Salary) AS TotalSalary, Gender
   FROM Employees
   GROUP BY Gender
   HAVING SUM(Salary) > 10;
   

2. Sum of salary grouped by department with address filtering

   SELECT SUM(Salary) AS TotalSalary, DepartmentID
   FROM Employees
   WHERE Address LIKE 'a%'
   GROUP BY DepartmentID
   HAVING SUM(Salary) > 12000;
   
   

3. Maximum salary grouped by address, filtered by SSN count

   SELECT MAX(Salary) AS MaxSalary, Address
   FROM Employees
   WHERE DepartmentID IN (10, 30)
   GROUP BY Address
   HAVING COUNT(SSN) > 3;
   
   

4. Average of DepartmentID ignoring NULL values

   SELECT AVG(ISNULL(DepartmentID, 0)) AS AvgDepartmentID
   FROM Employees;
   
   

5. Sum of DepartmentID divided by total count (without ignoring NULL)

   SELECT SUM(DepartmentID) / COUNT(*) AS AvgDepartmentID
   FROM Employees;
   
   

Grouping by Multiple Attributes

1. Sum of salary grouped by department number and department name (using INNER JOIN with Departments table)

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

2. Sum of salary grouped by department number and address

   SELECT SUM(Salary) AS TotalSalary, DepartmentID, Address
   FROM Employees
   GROUP BY DepartmentID, Address;