SQL - Aggregate Functions
You want to find the department with the highest average salary and also show the total number of employees in that department. Which query correctly combines these aggregates?
You want to find the department with the highest average salary and also show the total number of employees in that department. Which query correctly combines these aggregates?
hard📝 Application Q8 of 15
Step-by-Step Solution
Solution:
Step 1: Group by department to get aggregates per department
COUNT and AVG require GROUP BY department to calculate per department.Step 2: Order by AVG(salary) descending and limit to top
ORDER BY AVG(salary) DESC LIMIT 1 selects department with highest average salary.Step 3: Validate other options
SELECT department, COUNT(*), AVG(salary) FROM employees ORDER BY AVG(salary) DESC LIMIT 1; lacks GROUP BY, C uses invalid HAVING with MAX(AVG(salary)), A misuses WHERE with aggregate.Final Answer:
SELECT department, COUNT(*), AVG(salary) FROM employees GROUP BY department ORDER BY AVG(salary) DESC LIMIT 1; -> Option BQuick Check:
GROUP BY + ORDER BY + LIMIT finds top aggregate [OK]
Quick Trick: Use ORDER BY AVG(...) DESC LIMIT 1 for top group [OK]
Common Mistakes:
MISTAKES- Missing GROUP BY when using aggregates
- Using HAVING with nested aggregates incorrectly
- Trying to filter aggregates in WHERE clause
Master "Aggregate Functions" in SQL
Want More Practice?
15+ quiz questions · All difficulty levels · Free
Free Signup - Practice All QuestionsMore SQL Quizzes