w3resource

MySQL Aggregate Function Exercises: Get the average salary for all departments employing more than 10 employees

MySQL Aggregate Function: Exercise-14 with Solution

Write a query to get the average salary for all departments employing more than 10 employees.

Sample table: employees


Code:

-- Calculating the average salary and counting the number of employees for each department, filtering out departments with fewer than 10 employees
SELECT department_id, AVG(salary), COUNT(*) 
-- Selecting data from the employees table
FROM employees 
-- Grouping the result set by department_id
GROUP BY department_id
-- Filtering the result set to include only groups where the count of employees is greater than 10
HAVING COUNT(*) > 10;

Explanation:

  • This SQL query calculates the average salary and counts the number of employees for each department from the employees table.
  • The SELECT statement selects the department_id column along with the average salary (AVG(salary)) and the count of employees (COUNT(*)) for each department.
  • The FROM clause specifies the employees table from which the data is being selected.
  • The GROUP BY clause groups the result set by the department_id column, allowing for the calculation of statistics for each department.
  • The HAVING clause filters the grouped results to include only those groups where the count of employees is greater than 10.

Relational Algebra Expression:

Relational Algebra Expression: Aggregate Function: Get the average salary for all departments employing more than 10 employees.

Relational Algebra Tree:

Relational Algebra Tree: Aggregate Function: Get the average salary for all departments employing more than 10 employees.

Pictorial Presentation of the above query

Pictorial: Query to get the average salary for all departments employing more than 10 employees.

 

MySQL Code Editor:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous:Write a query to get the job ID and maximum salary of the employees where maximum salary is greater than or equal to $4000
Next:MySQL Subquery

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.