GROUP BY & Aggregates

Aggregate functions perform a calculation on a set of values and return a single value. They are often used with the GROUP BY clause.

Common Aggregate Functions

COUNT(*) vs COUNT(column)

This is a frequent interview topic:

• COUNT(*) counts every row in the result set, including rows with NULL values.
• COUNT(column_name) counts only the rows where the specified column is not NULL.

-- Total employees = 100, those with emails = 85
SELECT COUNT(*), COUNT(email) FROM employees;
-- Result: 100, 85

GROUP BY Basics

GROUP BY groups rows that have the same values into summary rows:

SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;

GROUP BY Rules

Every column in the SELECT list must either:

• Appear in the GROUP BY clause, or
• Be wrapped in an aggregate function

Violating this rule causes an error in most databases (PostgreSQL, SQL Server, Oracle). MySQL is lenient but produces unpredictable results.

The HAVING Clause

WHERE filters rows before grouping. HAVING filters groups after grouping. You cannot use aggregate functions in WHERE.

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 70000;

GROUP BY with Multiple Columns

SELECT department_id, job_role, COUNT(*) AS count
FROM employees
GROUP BY department_id, job_role;

GROUP BY with Expressions

You can group by computed values:

SELECT YEAR(hire_date) AS hire_year, COUNT(*) AS hired
FROM employees
GROUP BY YEAR(hire_date);

Note: In some databases, you must repeat the expression in GROUP BY — you cannot use the alias.

ROLLUP and CUBE

For subtotals and grand totals, databases support extensions like ROLLUP:

SELECT department_id, job_role, SUM(salary)
FROM employees
GROUP BY ROLLUP (department_id, job_role);

SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;

SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id;

SELECT email, COUNT(email)
FROM users
GROUP BY email
HAVING COUNT(email) > 1;

SELECT sale_date, amount,
       SUM(amount) OVER (ORDER BY sale_date) as running_total
FROM sales;

SELECT department_id,
       COUNT(*) AS emp_count,
       ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY department_id;