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 Tree:
Pictorial Presentation of the above query
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?
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics