SQL Aggregation Interview Questions

GROUP BY collapses rows that share the same value in one or more columns into a single row, allowing you to apply aggregate functions like COUNT, SUM, AVG, MIN, and MAX to each group. Without GROUP BY, aggregate functions operate on the entire result set as one group. A classic use case is calculating total sales per region or counting users per signup date.

SELECT region, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY region
ORDER BY total_sales DESC;

Aggregate functions operate on a set of rows and return a single summary value. Common examples are SUM(), COUNT(), AVG(), MIN(), and MAX(). They are typically used with GROUP BY to compute values per group. Scalar functions, on the other hand, operate on a single value and return a single value. Examples include UPPER(), LEN(), ROUND(), and GETDATE(). The distinction is that aggregate functions collapse multiple rows into one result, while scalar functions transform one input into one output on a row-by-row basis.

-- Aggregate: summarizes multiple rows
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

-- Scalar: transforms a single value per row
SELECT UPPER(first_name), ROUND(salary, 2) FROM employees;