Reporting Aggregated Data with Group Functions practice questions

Worked examples for Reporting Aggregated Data with Group Functions

1. Question 1

   Every row in the EMPLOYEES table has a non-null DEPT_ID, and the COMMISSION column is null for every employee who earns no commission. You must return one row for each department id that appears in EMPLOYEES, showing that id together with the number of employees in that department whose COMMISSION is null. A department in which every employee does have a commission must still appear in the result, reporting 0. Which query produces exactly that result?

   1. A. SELECT dept_id, COUNT(commission) AS no_commission FROM employees GROUP BY dept_id

      Inverts the requirement: COUNT(column) counts the rows where the column is NOT null, so this reports how many employees in each department DO have a commission — the complement of the number asked for.

   2. B. SELECT dept_id, COUNT(CASE WHEN commission IS NULL THEN 1 ELSE 0 END) AS no_commission FROM employees GROUP BY dept_id

      Represents the misconception that COUNT sums the CASE result. The ELSE 0 branch yields the non-null value 0, and COUNT counts every non-null argument, so this returns the total number of employees in each department regardless of commission. Only SUM(...) — not COUNT(...) — may safely use ELSE 0.

   3. C. SELECT dept_id, COUNT(CASE WHEN commission IS NULL THEN 1 END) AS no_commission FROM employees GROUP BY dept_idCorrect answer

      With no ELSE branch, the CASE expression evaluates to NULL for any employee who has a commission, and COUNT(expr) counts only non-null values, so exactly the null-commission employees are counted; a department with none produces 0 rather than NULL, because COUNT never returns null for a group that exists.

   4. D. SELECT dept_id, COUNT(*) AS no_commission FROM employees WHERE commission = NULL GROUP BY dept_id

      Represents the misconception that = NULL tests for nullness. Any comparison with NULL evaluates to UNKNOWN, never TRUE, so the WHERE clause admits no rows at all and the query returns an empty result set; IS NULL is the only test that works.

   Explanation

   COUNT(expr) tallies only the rows in the group for which expr is not null, so wrapping a CASE expression that deliberately returns NULL for the unwanted rows turns COUNT into a conditional counter. Adding an ELSE 0 branch defeats this, because 0 is a non-null value and is therefore counted like any other. Nullness itself must be tested with IS NULL, since an equality comparison against NULL yields UNKNOWN and filters every row away.

2. Question 2

   The EMPLOYEES table has a DEPT_ID column that is never null and a COMMISSION column that is null for several employees. You must return one row for every department id that appears in EMPLOYEES, showing that id together with the average commission of the department when an employee who has no commission is counted as a commission of 0 — so an employee with a null commission must still contribute to the divisor, and a department in which every employee has a null commission must report 0 rather than null. Which query produces exactly that result?

   1. A. SELECT dept_id, AVG(commission) AS avg_comm FROM employees GROUP BY dept_id;

      Assumes AVG treats a null as 0. AVG ignores null arguments entirely, so it divides by the number of NON-NULL commissions, not by the number of employees, and returns null for a department whose commissions are all null.

   2. B. SELECT dept_id, NVL(AVG(commission), 0) AS avg_comm FROM employees GROUP BY dept_id;

      Applies NVL after aggregation. The nulls are still skipped by AVG, so the divisor is the non-null count; NVL only rewrites the final null of an all-null department to 0, leaving every mixed department's average too high.

   3. C. SELECT dept_id, AVG(NVL(commission, 0)) AS avg_comm FROM employees GROUP BY dept_id;Correct answer

      NVL is applied to the argument before aggregation, so every row of the group supplies a non-null value: the nulls become 0, they are counted in the divisor, and an all-null department averages 0 instead of null. This is the only form that satisfies both stated requirements.

   4. D. SELECT dept_id, SUM(commission)/COUNT(*) AS avg_comm FROM employees GROUP BY dept_id;

      Assumes SUM over an all-null group yields 0. SUM also ignores nulls and returns null when every value in the group is null, and null divided by a count is null — so an all-null department reports null, not the required 0.

   Explanation

   Group functions other than COUNT() discard null arguments before they aggregate, which affects both the numerator and the divisor: AVG(commission) sums the non-null commissions and divides by how many of them there were. To make a missing commission count as a real zero, the null must be replaced in the argument, before the function sees it. Replacing the null in the result instead only repairs the all-null group and leaves every partially populated group averaging over too few rows, and SUM/COUNT() arithmetic still collapses to null whenever the whole group is null.

3. Question 3

   The EMPLOYEES table contains exactly the eight rows shown below (only the relevant columns are listed; COMMISSION is defined as NUMBER(4,2)).

   EMP_ID	FIRST_NAME	COMMISSION
   100	Alice	(null)
   101	Bob	0.10
   102	Carol	0.15
   103	Dave	(null)
   104	Eve	0.05
   105	Frank	(null)
   106	Grace	0.20
   107	Heidi	(null)

   What value does the following query return?

   SELECT COUNT(*),
          COUNT(commission),
          SUM(commission * 100),
          AVG(commission * 100)
     FROM employees

   1. A. 8, 4, 50, 6.25

      Applies the null-skipping rule to COUNT(commission) and SUM but not to AVG, dividing the total by the 8 rows in the table instead of by the 4 non-null commission values. AVG's denominator is COUNT(expr), not COUNT(*).

   2. B. 8, 4, 50, 12.5Correct answer

      COUNT(*) counts all 8 rows; COUNT(commission) counts only the 4 non-null commissions; SUM ignores nulls giving 10+15+5+20 = 50; and AVG is SUM over COUNT of the non-null values, 50/4 = 12.5.

   3. C. 8, 8, 50, 6.25

      Treats null commissions as zero. If nulls behaved as 0 then COUNT(commission) would be 8 and AVG would be 50/8 = 6.25; in fact null is 'unknown', so it is excluded from both the count and the average rather than contributing a zero.

   4. D. 4, 4, 50, 12.5

      Assumes COUNT() also skips rows that contain a null. COUNT() is the one aggregate that never inspects column values — it counts every row in the group, including all-null rows.

   Explanation

   All Oracle aggregate functions except COUNT(), GROUPING, and GROUPING_ID ignore nulls in their argument. So COUNT() reports the full row count while COUNT(expr), SUM(expr), and AVG(expr) see only the rows where expr is not null. Crucially, AVG(expr) is defined as SUM(expr) divided by COUNT(expr) — the count of non-null values — so an average over a partly null column is larger than the same total divided by the row count. Using NVL(expr, 0) inside the aggregate is what would make the nulls participate as zeros.

4. Question 4

   The employees table holds one row per employee. Its commission column is nullable: an employee who earns no commission has commission set to NULL, and no employee has a commission of 0.

   Which query returns a single value: the average commission per employee across every row in employees, counting an employee who earns no commission as a commission of 0?

   1. A. SELECT AVG(NVL(commission, 0)) FROM employeesCorrect answer

      NVL is applied per row, before aggregation, so every no-commission employee contributes a real 0 rather than a NULL. AVG then sees a non-null value in all rows and divides the total by the full row count, which is exactly the requested 'count a missing commission as 0' average (Aggregate Functions: all aggregates except COUNT(*), GROUPING and GROUPING_ID ignore nulls).

   2. B. SELECT SUM(commission) / COUNT(commission) FROM employees

      Assumes COUNT(commission) counts every row. COUNT(<column>) ignores NULLs just as SUM does, so this divides the commission total only by the number of employees who actually earn a commission — it is arithmetically identical to plain AVG(commission) and never counts the no-commission employees at all.

   3. C. SELECT AVG(DISTINCT NVL(commission, 0)) FROM employees

      Treats DISTINCT inside an aggregate as harmless. DISTINCT collapses duplicate values before averaging, so all of the substituted 0 values become a single 0 and every repeated commission rate is counted once — the denominator becomes the number of distinct rates, not the number of employees.

   4. D. SELECT NVL(AVG(commission), 0) FROM employees

      Applies NVL after aggregation instead of before it. AVG has already discarded the NULL rows by then, so the substitution only fires in the one case where AVG itself returns NULL (no rows, or every commission NULL); with any non-null commission present the result is the plain AVG over non-null values.

   Explanation

   Every aggregate function except COUNT(*), GROUPING and GROUPING_ID ignores NULL inputs, so AVG's denominator is the number of non-null values, not the number of rows. To make missing values participate as 0 they must be replaced row by row, inside the aggregate's argument, so the aggregate sees a value in every row. Substituting after the aggregate has run is too late — the NULL rows have already been dropped from both the sum and the count — and adding DISTINCT changes the denominator again by collapsing repeated values.

5. Question 5

   The employees table has a dept_id column (every employee belongs to a department) and a nullable commission column. Which query returns one row for every department id that appears in employees — including a department in which no employee has a commission, which must show 0 — with the department id and the number of employees in that department whose commission is not null?

   1. A. SELECT dept_id, COUNT(*) FROM employees GROUP BY dept_id

      Assumes COUNT() skips rows that are null in the column of interest. COUNT() counts every row in the group regardless of any column's nullity, so a department whose employees all have a null commission reports its full headcount instead of 0.

   2. B. SELECT dept_id, COUNT(*) FROM employees WHERE commission IS NOT NULL GROUP BY dept_id

      Assumes filtering the nulls in WHERE is equivalent to letting COUNT ignore them. WHERE removes the rows before grouping, so a department whose employees all lack a commission contributes no rows at all and is missing from the result rather than showing 0.

   3. C. SELECT dept_id, COUNT(commission) FROM employees GROUP BY dept_idCorrect answer

      COUNT(expr) returns the number of rows in the group where expr is not null, so each department reports only its commissioned employees. Grouping by dept_id alone produces exactly one row per department present in the table, and a group with no non-null commission yields 0 (COUNT never returns null).

   4. D. SELECT dept_id, COUNT(commission) FROM employees GROUP BY dept_id, commission

      Assumes the aggregated column may be added to GROUP BY without changing the grouping. Adding commission makes the group key (dept_id, commission), so the query returns one row per distinct commission value within each department — several rows per department, each counting 1 (or 0 for the null commission group).

   Explanation

   COUNT(column) counts only the rows whose column value is not null, while COUNT(*) counts every row in the group; that difference is what makes an aggregate report commissioned employees rather than total headcount. Excluding the nulls in WHERE is not equivalent, because a group that loses all of its rows disappears from the result set instead of reporting zero. The GROUP BY list alone determines the result granularity, so listing the aggregated column there splits each department into one row per distinct value.

6. Question 6

   In the employees table, manager_id holds the emp_id of an employee's manager and is NULL for the two employees who report to no one. Which query returns exactly two columns — the ID of every manager who has more than one direct report and that manager's number of direct reports — while returning no row for the managerless employees?

   1. A. SELECT manager_id, COUNT(*) AS direct_reports FROM employees GROUP BY manager_id HAVING COUNT(*) > 1

      GROUP BY does not discard rows whose grouping key is NULL — it collects all of them into a single group. The two managerless employees therefore form a group of 2, which passes HAVING COUNT(*) > 1, so this query returns an extra row with a NULL manager ID alongside the genuine managers.

   2. B. SELECT manager_id, COUNT(*) AS direct_reports FROM employees WHERE manager_id IS NOT NULL AND COUNT(*) > 1 GROUP BY manager_id

      WHERE is evaluated before any grouping takes place, so no aggregate value exists yet. Placing COUNT(*) there raises ORA-00934 'group function is not allowed here'; a count-based restriction must live in HAVING.

   3. C. SELECT manager_id, COUNT(*) AS direct_reports FROM employees WHERE manager_id IS NOT NULL GROUP BY dept_id HAVING COUNT(*) > 1

      Every non-aggregate expression in the SELECT list must appear in GROUP BY. Grouping by dept_id while selecting the bare manager_id raises ORA-00979 'not a GROUP BY expression'; Oracle will not pick an arbitrary manager_id per department group.

   4. D. SELECT manager_id, COUNT(*) AS direct_reports FROM employees WHERE manager_id IS NOT NULL GROUP BY manager_id HAVING COUNT(*) > 1Correct answer

      The row-level test (manager_id IS NOT NULL) runs in WHERE, removing the managerless employees before groups are formed, and the group-level test (COUNT(*) > 1) runs in HAVING after grouping. Against this data it returns manager 100 with 3 reports and manager 106 with 2 reports, and manager 101 — with a single report — is dropped by HAVING.

   Explanation

   WHERE and HAVING act at different stages of query processing: WHERE eliminates individual rows before any grouping happens, GROUP BY then forms the groups, and HAVING finally eliminates whole groups using aggregates or grouped expressions. Because GROUP BY gathers every row with a NULL grouping key into one group rather than dropping those rows, excluding managerless employees requires a row-level predicate in WHERE, while a count restriction can only be expressed after grouping. An aggregate used in WHERE raises ORA-00934, and a bare column that is neither aggregated nor listed in GROUP BY raises ORA-00979.

7. Question 7

   The employees table is defined with the columns emp_id, first_name, last_name, salary, commission, manager_id, dept_id and hire_date. What is the result of executing the following statement?

   SELECT dept_id, last_name, COUNT(*)
   FROM   employees
   WHERE  salary > 4000
   GROUP  BY dept_id
   HAVING COUNT(*) > 1

   1. A. ORA-00979: not a GROUP BY expressionCorrect answer

      last_name appears in the SELECT list but is neither an argument of a group function nor listed in the GROUP BY clause, which is exactly the condition ORA-00979 reports. Adding last_name to GROUP BY (or wrapping it in an aggregate such as MAX) is the fix.

   2. B. It executes successfully, returning one row per qualifying department together with an arbitrary last_name taken from that department.

      This is the misconception that Oracle will silently pick some representative value for a column that is neither aggregated nor grouped. Oracle has no such rule: a bare column outside GROUP BY is a compile-time error, not an arbitrary pick.

   3. C. ORA-00934: group function is not allowed here

      Assumes an aggregate may not appear in HAVING. ORA-00934 is raised when a group function is used where it is forbidden (WHERE, GROUP BY); HAVING is precisely the clause designed to hold COUNT(*) > 1, so this condition is legal.

   4. D. ORA-00937: not a single-group group function

      Picks the code for the different failure where a bare column is mixed with an aggregate and there is NO GROUP BY clause at all. Here a GROUP BY clause is present, so Oracle reports the ungrouped-expression error ORA-00979 instead.

   Explanation

   Once a query has a GROUP BY clause, every expression in the SELECT list must either be a group function or appear in the GROUP BY clause; a column that is neither is rejected at parse time with "not a GROUP BY expression". Oracle never substitutes an arbitrary row value for such a column. The WHERE clause here filters individual rows before grouping and the HAVING clause filters the resulting groups, so both of those clauses are well formed — only the ungrouped SELECT column is at fault.

8. Question 8

   In the employees table, commission is NULL for an employee who earns no commission, and every employee row carries a dept_id. Which query returns one row for each department that has at least one employee, showing the department id together with the number of that department's employees whose commission is NULL?

   1. A. SELECT dept_id, COUNT(*) - COUNT(commission) FROM employees GROUP BY dept_idCorrect answer

      COUNT(*) counts all rows in the group including nulls, while COUNT(commission) counts only rows whose commission is not null; the difference is precisely the number of NULL commissions per department, and grouping by dept_id emits one row per department that has employees.

   2. B. SELECT dept_id, COUNT(commission) FROM employees GROUP BY dept_id

      Assumes COUNT(column) counts every row in the group. COUNT(expr) counts only rows where expr is NOT NULL, so this returns the number of employees who DO have a commission — the exact complement of what was asked.

   3. C. SELECT dept_id, COUNT(NVL(commission, 0)) FROM employees GROUP BY dept_id

      Assumes NVL marks the missing commissions so COUNT can tally them. NVL evaluates before the aggregate and makes EVERY value non-NULL, so COUNT(NVL(commission,0)) equals COUNT(*) — the total employee count in each department, not the NULL count.

   4. D. SELECT dept_id, COUNT(*) FROM employees WHERE commission = NULL GROUP BY dept_id

      Tests a null with the equality operator. Any comparison with NULL evaluates to UNKNOWN, never TRUE, so the WHERE clause filters out every row and the query returns no rows at all; IS NULL is the only test that works.

   Explanation

   COUNT(*) is the only aggregate that counts rows unconditionally — it includes rows containing nulls — whereas COUNT(expr) evaluates expr per row and counts only the non-null results. Subtracting the second from the first inside a GROUP BY therefore yields the per-group count of missing values. Wrapping the column in NVL defeats this, because the substitution happens before aggregation and leaves nothing null to skip, and filtering with = NULL eliminates all rows because comparisons against NULL are never TRUE.