COUNT, SUM, AVG, MIN, MAX Functions

Explanation:

The COUNT function in SQL is used to count the number of rows in a result set or the number of non-NULL values in a column. For example:
       
       SELECT COUNT(*) FROM employees;

• This query counts the total number of rows in the employees table.

Explanation:

The SUM function is used to calculate the total sum of a numeric column. For example:
       
       SELECT SUM(salary) FROM employees;

• This query calculates the total salary of all employees.

Explanation:

The AVG function calculates the average value of a numeric column. For example:
       
       SELECT AVG(salary) FROM employees;

• This query calculates the average salary of all employees.

Explanation:

The MIN function is used to find the smallest value in a column. For example:
       
       SELECT MIN(salary) FROM employees;

• This query retrieves the smallest salary from the employees table.

Explanation:

The MAX function is used to find the largest value in a column. For example:
       
       SELECT MAX(salary) FROM employees;

• This query retrieves the largest salary from the employees table.

Explanation:

Yes, you can combine aggregate functions with the GROUP BY clause to group data and perform calculations on each group. For example:
       
       SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;

• This query calculates the average salary for each department.

Explanation:

Aggregate functions like COUNT, SUM, AVG, MIN, and MAX handle NULL values differently:
       • COUNT(*) counts all rows, including those with NULL values.
       • COUNT(column) counts non-NULL values.
       • SUM, AVG, MIN, and MAX ignore NULL values.
       • Example:
         
         SELECT COUNT(salary) FROM employees;

This query counts the number of employees with a non-NULL salary.

Explanation:

You can use multiple aggregate functions in a single query to perform different calculations. For example:
       
       SELECT department_id, COUNT(*) AS num_employees, SUM(salary) AS total_salary, AVG(salary) AS avg_salary FROM employees GROUP BY department_id;

• This query retrieves the number of employees, total salary, and average salary for each department.

Explanation:

You use aggregate functions with the HAVING clause to filter groups based on aggregate results. For example:
       
       SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id HAVING AVG(salary) > 5000;

• This query retrieves departments with an average salary greater than 5000.

Explanation:

Scenario: Suppose you need to generate a report showing the number of employees, total salary, average salary, minimum salary, and maximum salary for each department, and only include departments with more than 5 employees.
       • Implementation:
         
         SELECT department_id, 
                COUNT(*) AS num_employees, 
                SUM(salary) AS total_salary, 
                AVG(salary) AS avg_salary, 
                MIN(salary) AS min_salary, 
                MAX(salary) AS max_salary
         FROM employees
         GROUP BY department_id
         HAVING COUNT(*) > 5;

• This query retrieves the number of employees, total salary, average salary, minimum salary, and maximum salary for each department and filters departments with more than 5 employees using the HAVING clause.