Aggregate Functions

Aggregate functions compute a single value from a set of rows.

Concept

Aggregate functions compute a single value from a set of rows. Without GROUP BY, the whole table (after WHERE) is one group.

Number of rows Counts all rows, including rows with NULLs Number of non-NULL values in col COUNT(DISTINCT col) Number of distinct non-NULL values

Ignores NULL; NULL if no non-NULL values Average = SUM / COUNT(col)

Smallest value (numbers, dates, strings)

Why It Matters

Aggregates answer "how many", "how much", "average", "earliest", "latest" — the core of every report and KPI.

Syntax

SELECT AGG_FUNCTION([DISTINCT] expression) [AS alias]
FROM table
[WHERE condition];

Example

11.1 Basic aggregates COUNT(*) = 8 rows.

COUNT(dept_id) = 7 because Kiran's dept_id is NULL.

COUNT(DISTINCT dept_id) = 3 (departments 1, 2, 3; NULL not counted; Finance has no employees).

11.2 COUNT(*) vs COUNT(column) vs COUNT(DISTINCT column) (Countries: India, UK, Singapore, Italy.)

COUNT(1) is equivalent to COUNT(*); there is no performance difference in modern databases.

11.3 Aggregates with WHERE 11.4 NULL behavior in aggregates Decide deliberately: is a missing score "unknown" (ignore) or "zero" (count as 0)?

On an empty set: COUNT(*) returns 0, but SUM, AVG, MIN, MAX return NULL:

11.5 Integer averages 11.6 String aggregation (list values into one string) Result: Laptop, Monitor, Mouse

SELECT COUNT(*)                AS total_employees,
       COUNT(dept_id)          AS with_department,
       COUNT(DISTINCT dept_id) AS distinct_departments,
       SUM(salary)             AS payroll,
       AVG(salary)             AS avg_salary,
       MIN(salary)             AS min_salary,
       MAX(salary)             AS max_salary,
       MIN(hire_date)          AS first_hire,
       MAX(hire_date)          AS latest_hire
FROM employees;

SELECT COUNT(*) AS all_customers, COUNT(email) AS with_email, COUNT(DISTINCT country) AS countries
FROM customers;

SELECT COUNT(*) AS delivered_orders, SUM(total_amount) AS delivered_revenue
FROM orders
WHERE status = 'DELIVERED';

CREATE TABLE scores (student VARCHAR(10), score INT);
INSERT INTO scores VALUES ('A', 10), ('B', 20), ('C', NULL);

SELECT COUNT(*)               AS n_rows,
       COUNT(score)           AS n_scores,
       SUM(score)             AS total,
       AVG(score)             AS avg_ignoring_null,
       AVG(COALESCE(score, 0)) AS avg_null_as_zero
FROM scores;

SELECT COUNT(*), SUM(total_amount) FROM orders WHERE status = 'RETURNED';
-- 0, NULL     → use COALESCE(SUM(total_amount), 0) for reports

SELECT AVG(quantity) FROM order_items;
-- quantities: 1,1,1,1,2,1,2,1,1 → sum 11, count 9 → 1.2222...
-- PostgreSQL, MySQL, Oracle: 1.2222...
-- SQL Server: 1 (integer average of an INT column!)
-- SQLite: 1.2222...
SELECT AVG(CAST(quantity AS DECIMAL(10,2))) FROM order_items;   -- SQL Server fix

-- PostgreSQL, SQL Server 2017+
SELECT STRING_AGG(product_name, ', ' ORDER BY product_name) FROM products WHERE category = 'Electronics';
-- SQL Server syntax for ordering: STRING_AGG(product_name, ', ') WITHIN GROUP (ORDER BY product_name)
-- MySQL, SQLite (group_concat)
SELECT GROUP_CONCAT(product_name ORDER BY product_name SEPARATOR ', ') FROM products WHERE category = 'Electronics';
-- Oracle
SELECT LISTAGG(product_name, ', ') WITHIN GROUP (ORDER BY product_name) FROM products WHERE category = 'Electronics';

Expected Result

(Sum of prices = 75000 + 800 + 12000 + 50 + 18000 + 35000 = 140850; 140850 / 6 = 23475.)

SELECT ROUND(AVG(price), 2) AS avg_price, MAX(price) - MIN(price) AS price_range
FROM products;

Common Mistakes

Using COUNT(column) when you mean COUNT(*) (NULLs silently skipped).

Mixing aggregated and non-aggregated columns without GROUP BY: SELECT dept_id, COUNT(*) FROM employees; is an error in standard SQL (MySQL with ONLY_FULL_GROUP_BY disabled returns an arbitrary value — dangerous).

Using aggregates in WHERE: WHERE salary > AVG(salary) is invalid; use a subquery.

Expecting SUM of zero rows to be 0.

Integer AVG in SQL Server.

Best Practices

Wrap report sums in COALESCE(SUM(x), 0).

Cast to DECIMAL before averaging integers when precision matters.

Use COUNT(DISTINCT ...) carefully on huge tables (expensive); some systems offer approximate versions (APPROX_COUNT_DISTINCT).

Interview Relevance

Very high: "COUNT(*) vs COUNT(col) vs COUNT(DISTINCT col)?", "How do aggregates treat NULL?", "What does SUM return for no rows?", "Can you use an aggregate in WHERE?".

Practice Questions

Count products and the total stock units.

Find the average order value excluding cancelled orders.

Find the earliest and latest order dates.

Count customers with no email (two ways: COUNT(*) - COUNT(email) and WHERE email IS NULL).

Predict SELECT AVG(x) for values (4, NULL, 8, NULL).