Commission Averages That Ignore Half the Department
Meridian Retail's HR team runs a monthly commission report on an EMPLOYEES table with 240 rows across 14 departments. Only 46 employees have a non-null COMMISSION_PCT; the rest are salaried and earn no commission. The analyst writes:
SELECT department_id, COUNT(), AVG(commission_pct) AS avg_comm FROM employees GROUP BY department_id HAVING COUNT() > 10;
Department 80 has 34 employees, 6 of whom have commission values averaging 0.18. The report prints 0.18, but the sales director expects about 0.03 because only 6 of 34 employees earn commission. A second query that adds LAST_NAME to the SELECT list fails with ORA-00979. The team also wants a column showing the percentage of each department's employees who earn commission, and a filter so departments with 10 or fewer employees disappear from the result.
1. Explain why Department 80 shows 0.18 and write a query that gives the director the average commission across all 34 employees, rounded to four decimal places. State clearly what each expression counts.
Read the worked answer
The 0.18 is not an average over 34 people. AVG(commission_pct) ignores NULLs, so its divisor is the 6 non-null commission values, not COUNT(*) of 34. The arithmetic is roughly 0.18 * 6 / 34 = 0.0318, which matches the director's expectation.
If NULL means 'this employee has no commission plan', treating it as zero is a business decision that must be documented. The query is:
SELECT department_id,
COUNT(*) AS headcount,
COUNT(commission_pct) AS commission_earners,
ROUND(AVG(NVL(commission_pct, 0)), 4) AS avg_comm_all_staff
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 10;
COUNT(*) counts rows, including rows where COMMISSION_PCT is null. COUNT(commission_pct) counts only non-null values. AVG(NVL(commission_pct,0)) forces the denominator to include everyone. If HR actually wants the average among commission earners, keep AVG(commission_pct) and label it explicitly as an earner-only average; do not present it as a department average.
2. Write one query returning department_id, headcount, commission earners, percentage of earners, and average commission across all employees, keeping only departments with more than 10 employees. Explain why the filter cannot be a WHERE clause on COUNT(*).
Read the worked answer
The filter belongs in HAVING because it is a condition on an aggregate result. WHERE is evaluated before rows are grouped, so WHERE COUNT(*) > 10 is invalid: at that point there is no group count yet. HAVING is evaluated after grouping and can reference COUNT(*).
SELECT department_id,
COUNT(*) AS headcount,
COUNT(commission_pct) AS commission_earners,
ROUND(100 * COUNT(commission_pct) / COUNT(*), 1) AS pct_earners,
ROUND(AVG(NVL(commission_pct, 0)), 4) AS avg_comm_all_staff
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 10;
COUNT(*) cannot be zero inside a returned group, so the division is safe here. If the query were instead aggregating over an outer join or a filtered inline view, guard the denominator with NULLIF(COUNT(*),0). Also decide whether departments with a NULL DEPARTMENT_ID belong in the report; if not, a row-level WHERE department_id IS NOT NULL is appropriate and is separate from the HAVING group filter.
3. The query with LAST_NAME fails with ORA-00979. Give two valid ways to return employee last names alongside department salary aggregates, and explain how each changes the result grain.
Read the worked answer
LAST_NAME is not in the GROUP BY, and it is not inside an aggregate, so Oracle cannot know which last name to return for each department group. That is the ORA-00979 error.
Option 1 keeps detail rows and uses an analytic function:
SELECT employee_id, last_name, department_id, salary,
ROUND(AVG(salary) OVER (PARTITION BY department_id), 2) AS dept_avg_salary
FROM employees;
This returns one row per employee. The average is calculated across the department but repeated on each employee row, so summing that column would double-count.
Option 2 aggregates separately and joins back:
SELECT e.employee_id, e.last_name, e.department_id, e.salary,
d.dept_avg_salary
FROM employees e
JOIN (SELECT department_id, AVG(salary) AS dept_avg_salary
FROM employees
GROUP BY department_id) d
ON d.department_id = e.department_id;
This also returns employee-level rows and is useful when the aggregate needs further filtering or reuse. Adding LAST_NAME to GROUP BY would technically run, but because employee names are nearly unique it would change the grain to one row per employee, not one row per department, defeating the report's purpose.