How can you write a query to show each department's total salary and the overall average salary across all departments in one result?

hard📝 Application Q9 of 15

SQL - Aggregate Functions

ASELECT department, SUM(salary), AVG(salary) FROM employees GROUP BY department;

BSELECT department, SUM(salary), (SELECT AVG(salary) FROM employees) AS overall_avg FROM employees GROUP BY department;

CSELECT department, SUM(salary), AVG(salary) OVER (department) FROM employees GROUP BY department;

DSELECT department, SUM(salary), AVG(salary) FROM employees;

Step-by-Step Solution

Solution:

Step 1: Calculate total salary per department
SUM(salary) with GROUP BY department gives total per department.
Step 2: Calculate overall average salary separately
Subquery (SELECT AVG(salary) FROM employees) returns overall average salary.
Step 3: Combine both in one SELECT
SELECT department, SUM(salary), (SELECT AVG(salary) FROM employees) AS overall_avg FROM employees GROUP BY department; combines per-department sum and overall average in one result.
Final Answer:
SELECT department, SUM(salary), (SELECT AVG(salary) FROM employees) AS overall_avg FROM employees GROUP BY department; -> Option B
Quick Check:
Subquery for overall average + GROUP BY for totals [OK]

Quick Trick: Use subquery for overall aggregate with GROUP BY [OK]

Common Mistakes:

MISTAKES

Master "Aggregate Functions" in SQL

9 interactive learning modes - each teaches the same concept differently

Want More Practice?

15+ quiz questions · All difficulty levels · Free

More SQL Quizzes