SQL aggregate functions interview questions, with answers

The questions, with answers

1. 1.What are the standard aggregate functions, and what do they return on an empty set?

   COUNT, SUM, AVG, MIN and MAX are the five in every database; most add STRING_AGG or GROUP_CONCAT, and statistical ones such as STDDEV and VARIANCE. An aggregate collapses many rows into one value, and it is the empty case that gets asked: COUNT of no rows is 0, but SUM, AVG, MIN and MAX of no rows are all NULL, not zero. So SELECT SUM(amount) FROM Orders WHERE customer_id = 999 returns NULL for a customer with no orders, and any arithmetic on it stays NULL. Wrap it — COALESCE(SUM(amount), 0) — when a report needs a number. With no GROUP BY, an aggregate query always returns exactly one row, even over an empty table.

2. 2.What does DISTINCT do inside an aggregate function?

   It deduplicates the values before the function sees them. COUNT(DISTINCT city) counts different cities; SUM(DISTINCT amount) adds each distinct amount once, which is almost never what you want for money but is a favourite trick question. The classic use is counting entities across a one-to-many join: after joining Customers to Orders, COUNT(customer_id) counts orders, COUNT(DISTINCT customer_id) counts customers. DISTINCT applies per group, so with GROUP BY student_id, COUNT(DISTINCT subject) gives each student's number of different subjects even when they have several rows per subject. Most databases allow only one DISTINCT column per aggregate; MySQL's COUNT(DISTINCT a, b) is an extension.

   SELECT c.city,
          COUNT(o.order_id)           AS orders,
          COUNT(DISTINCT o.customer_id) AS buying_customers
   FROM Customers c
   JOIN Orders o ON o.customer_id = c.customer_id
   GROUP BY c.city;

3. 3.What is conditional aggregation, and how does it produce a pivot?

   Putting a CASE expression inside the aggregate so that each row contributes only when a condition holds. SUM(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END) counts shipped orders; SUM(CASE WHEN status = 'shipped' THEN amount END) totals their value (rows that fail the condition yield NULL, which SUM ignores). Put several side by side and you have turned rows into columns — one row per customer with a column per status — which is the standard answer to "pivot this without a PIVOT keyword". PostgreSQL and SQLite offer the tidier FILTER clause: COUNT(*) FILTER (WHERE status = 'shipped'). The interview point is that one pass over the table computes every column at once, instead of a query per status.

   SELECT customer_id,
          SUM(CASE WHEN status = 'shipped'   THEN 1 ELSE 0 END) AS shipped,
          SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
          SUM(CASE WHEN status = 'shipped'   THEN amount END)   AS shipped_value
   FROM Orders
   GROUP BY customer_id;

4. 4.Do MIN and MAX work on strings and dates?

   Yes — they work on anything with an ordering. MAX(order_date) is the most recent order; MIN(name) is the alphabetically first name under the column's collation, which decides whether 'apple' sorts before 'Banana' (case-insensitive collations, MySQL's default, say yes; PostgreSQL's default C or locale collation may say no). The follow-up trap is asking for the row that has the maximum: SELECT name, MAX(salary) FROM Employees returns a random name next to the top salary in permissive MySQL modes and an error in standard SQL, because name is neither grouped nor aggregated. Use ORDER BY salary DESC LIMIT 1, or a subquery WHERE salary = (SELECT MAX(salary) ...), which also returns every row tied at the top.

5. 5.How do you concatenate values from several rows into one string?

   With a string aggregate: STRING_AGG(name, ', ') in PostgreSQL and SQL Server 2017+, GROUP_CONCAT(name SEPARATOR ', ') in MySQL and SQLite, LISTAGG in Oracle. Two details interviewers ask about. Ordering: the result order is undefined unless you say it — STRING_AGG(name, ', ' ORDER BY name) in PostgreSQL, GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') in MySQL, WITHIN GROUP (ORDER BY name) in SQL Server. Length: MySQL truncates the result at group_concat_max_len, 1024 bytes by default, silently, which has produced many wrong reports; raise the setting or rethink the query. NULL values are skipped, like every other aggregate.

   -- PostgreSQL
   SELECT dept_id, STRING_AGG(name, ', ' ORDER BY name) AS members
   FROM Employees
   GROUP BY dept_id;
   
   -- MySQL
   SELECT dept_id, GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS members
   FROM Employees
   GROUP BY dept_id;

6. 6.Can you nest one aggregate inside another, like MAX(COUNT(*))?

   No — aggregates cannot be nested in a single query level, because the inner one has already collapsed the rows the outer one would need. "The department with the most employees" is the question that exposes this. Compute the counts per department in a subquery or CTE, then take the maximum or the top row in the outer query. ORDER BY the count with LIMIT 1 is the simplest form; MAX over the derived table is the set-based form; and if several departments tie you need the subquery form (WHERE cnt = (SELECT MAX(cnt) ...)) or a window function such as RANK to return them all.

   WITH counts AS (
     SELECT dept_id, COUNT(*) AS cnt
     FROM Employees
     GROUP BY dept_id
   )
   SELECT dept_id, cnt
   FROM counts
   WHERE cnt = (SELECT MAX(cnt) FROM counts);   -- ties included

7. 7.Why does AVG of integer columns sometimes return an integer?

   Because some databases keep the input type. In SQL Server, AVG over an INT column does integer division: the average of 1, 2 and 4 comes back as 2, not 2.333. MySQL and PostgreSQL return a decimal or numeric result, so they give 2.3333. Oracle also returns the exact value. The portable fix is to convert before averaging — AVG(CAST(marks AS DECIMAL(10,2))) or AVG(marks * 1.0) — so the arithmetic is done in decimal everywhere. The same type rule affects SUM: summing a 32-bit INT column that overflows two billion raises an error in SQL Server, while PostgreSQL promotes SUM of integers to BIGINT and SUM of BIGINT to NUMERIC. Knowing which database you are on is part of the answer.

8. 8.Is there any difference between COUNT(*), COUNT(1) and SUM(1)?

   COUNT(*) and COUNT(1) return the same number — the count of rows — and in every mainstream database they also run the same way; the belief that COUNT(1) is faster because it "doesn't read all columns" is a myth, since COUNT(*) never reads column data either, it counts rows. SUM(1) also gives the row count for a non-empty group, but it differs on an empty result: COUNT returns 0 and SUM returns NULL, because SUM of nothing is NULL. So SUM(1) is a slower, edge-prone way to write COUNT(*). The only variant that changes the answer is COUNT(column), which skips NULLs — that one is a genuinely different question, not a style choice.

How the diagnostic asks it

One question from the SQL bank, exactly as a sitting would show it. The bank has 5 on aggregate functions and 60 across SQL.