Menu
CoddyTech
Start learning
Learn SQL step by stepOnline SQL compiler

SQL GROUP BY and Aggregate Function Interview Questions

Aggregates, GROUP BY and HAVING, conditional counts, string aggregation and subtotals.

By Kevin Spektor, Co-founder & CTO

10 questions

  1. What are aggregate functions in SQL?

    They turn many rows into one value: COUNT, SUM, AVG, MIN and MAX. With GROUP BY they return one value per group.

    SQL
    CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
SELECT dept,
       COUNT(*) AS people,
       SUM(salary) AS payroll,
       AVG(salary) AS average,
       MIN(salary) AS lowest,
       MAX(salary) AS highest
FROM employees
GROUP BY dept
ORDER BY dept;

    Every aggregate except COUNT(*) ignores NULLs. See aggregate functions.

  2. How does AVG treat NULL values?

    SQL
    CREATE TABLE bonuses (name TEXT, bonus INTEGER);
INSERT INTO bonuses VALUES ('Asha', 100), ('Ravi', NULL), ('Meera', 200);
SELECT AVG(bonus) FROM bonuses;

    AVG skips NULLs, so it averages 100 and 200: 150.0. If NULL means "no bonus", use AVG(COALESCE(bonus, 0)), which gives 100.0. In SQL Server, AVG of an INT column returns an INT, so cast to DECIMAL first.

  3. Why do you get "column must appear in the GROUP BY clause or be used in an aggregate function"?

    Because a selected column has several values per group and you did not say which one to show: after GROUP BY dept, name has three values for Engineering.

    PostgreSQL, SQL Server, Oracle and MySQL with ONLY_FULL_GROUP_BY (the default since 5.7.5) reject it. SQLite accepts it, and with a single MAX or MIN takes the bare columns from that row:

    SQL
    CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
SELECT dept, name, MAX(salary)
FROM employees
GROUP BY dept
ORDER BY dept;

    That works here but fails in PostgreSQL. The portable version uses RANK() OVER (PARTITION BY dept ORDER BY salary DESC).

  4. Which filter runs first, WHERE or HAVING?

    SQL
    CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
SELECT COUNT(*) FROM (
  SELECT dept
  FROM employees
  WHERE salary > 75000
  GROUP BY dept
  HAVING COUNT(*) > 1
) AS t;

    WHERE salary > 75000 runs first and leaves three Engineering rows and Priya from Sales. HAVING COUNT(*) > 1 keeps only Engineering, so the outer query counts 1. Answering 2 forgets that John (70000) was filtered out before the count.

  5. How do you count only the rows that match a condition inside a GROUP BY?

    Put a CASE inside the aggregate: SUM(CASE WHEN condition THEN 1 ELSE 0 END). PostgreSQL and SQLite 3.30+ also have the standard FILTER clause; MySQL and SQL Server do not.

    SQL
    CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
SELECT dept,
       COUNT(*) AS people,
       SUM(CASE WHEN salary >= 85000 THEN 1 ELSE 0 END) AS high_paid,
       COUNT(*) FILTER (WHERE salary < 85000) AS others
FROM employees
GROUP BY dept
ORDER BY dept;

    It computes several counts in one pass, and it is also how you pivot rows into columns.

  6. How do you combine values from many rows into one comma-separated string?

    Use the string aggregate: GROUP_CONCAT in MySQL and SQLite, STRING_AGG in PostgreSQL and SQL Server 2017+, LISTAGG in Oracle.

    SQL
    CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
SELECT dept, GROUP_CONCAT(name, ', ' ORDER BY name) AS people
FROM employees
GROUP BY dept
ORDER BY dept;

    Without an ORDER BY inside the aggregate the order is not guaranteed (SQLite accepts it from 3.44). MySQL cuts the result at group_concat_max_len, 1024 bytes by default.

  7. What do ROLLUP, CUBE and GROUPING SETS do?

    They add subtotal rows in one query. ROLLUP (a, b) groups by (a, b), then (a), then the grand total; CUBE groups by every combination; GROUPING SETS lists exactly the groupings you want.

    -- PostgreSQL, SQL Server, Oracle
SELECT dept, SUM(salary) FROM employees GROUP BY ROLLUP (dept);
-- MySQL (only ROLLUP; no CUBE or GROUPING SETS)
SELECT dept, SUM(salary) FROM employees GROUP BY dept WITH ROLLUP;

    GROUPING(dept) returns 1 on a subtotal row, telling it apart from a real NULL. SQLite has none of these, so use UNION ALL:

    SQL
    CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
SELECT dept, total FROM (
  SELECT 0 AS k, dept, SUM(salary) AS total FROM employees GROUP BY dept
  UNION ALL
  SELECT 1, 'All departments', SUM(salary) FROM employees
) AS t
ORDER BY k, dept;

    The last row is the grand total, 512000.

  8. What is the difference between DISTINCT and GROUP BY?

    Without aggregates they return the same rows and usually get the same plan. GROUP BY exists to compute aggregates per group; DISTINCT only removes duplicate result rows.

    SQL
    CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
SELECT
  (SELECT COUNT(*) FROM (SELECT DISTINCT dept FROM employees) AS d) AS distinct_rows,
  (SELECT COUNT(*) FROM (SELECT dept FROM employees GROUP BY dept) AS g) AS grouped_rows;

    Both return 3. A DISTINCT added to hide duplicates from a bad join is a smell; fix the join.

  9. How do you GROUP BY multiple columns?

    List them all in GROUP BY: the query returns one row per distinct combination of their values.

    SQL
    CREATE TABLE sales (region TEXT, product TEXT, amount INTEGER);
INSERT INTO sales VALUES
  ('North', 'Pen', 100), ('North', 'Pen', 50), ('North', 'Book', 200),
  ('South', 'Pen', 80), ('South', 'Book', 120), ('South', 'Book', 30);
SELECT region, product, COUNT(*) AS orders, SUM(amount) AS revenue
FROM sales
GROUP BY region, product
ORDER BY region, product;

    North and South each sold pens and books, so there are four groups, and North's pens are 2 orders worth 150. The column order inside GROUP BY does not change the groups; ORDER BY decides the output order. Every selected column that is not aggregated must be in the list. See GROUP BY and HAVING.

  10. Does GROUP BY put rows with a NULL key into one group or a group each?

    SQL
    CREATE TABLE employees (name TEXT, dept TEXT);
INSERT INTO employees VALUES
  ('Asha', 'Sales'), ('Ravi', NULL), ('Meera', NULL), ('John', 'Sales'), ('Karan', 'HR');
SELECT COUNT(*) FROM (SELECT dept FROM employees GROUP BY dept) AS t;

    It prints 3: Sales, HR, and one group for both NULL departments. NULL = NULL is unknown in a WHERE, but GROUP BY, DISTINCT and UNION treat NULLs as equal, so they collapse into one group. In a report, label it with COALESCE(dept, 'No department').

Coddy programming languages illustration

Learn to code with Coddy

Get started