GROUP BY & Aggregate Functions

Aggregate functions collapse many rows into a single summary value — a count, a total, an average. GROUP BY lets you compute those summaries per group instead of across the whole table: total fees per course, average age per city, number of students per batch. Together they turn raw rows into the answers people actually ask for.

The five aggregate functions

These are the workhorses, all standard SQL:

• COUNT() — how many rows
• SUM() — total of a numeric column
• AVG() — average of a numeric column
• MIN() — smallest value
• MAX() — largest value

Used without grouping, they summarise the entire table:

-- PostgreSQL / MySQL compatible

-- One summary row for the whole table
SELECT
    COUNT(*)        AS total_students,
    AVG(age)        AS average_age,
    MIN(age)        AS youngest,
    MAX(age)        AS oldest
FROM student;

GROUP BY: one summary per group

GROUP BY splits rows into groups by one or more columns, then applies the aggregate to each group:

-- How many students per city?
SELECT
    city,
    COUNT(*) AS student_count
FROM student
GROUP BY city;

-- Result:
-- Jalgaon | 12
-- Pune    | 7
-- Nashik  | 4

You can group by several columns to get finer breakdowns:

-- Students per city AND age bracket flag
SELECT city, (age >= 18) AS is_adult, COUNT(*) AS n
FROM student
GROUP BY city, (age >= 18);

COUNT(*) vs COUNT(column) vs COUNT(DISTINCT)

A subtle but important distinction:

SELECT
    COUNT(*)              AS all_rows,          -- counts every row
    COUNT(phone)          AS rows_with_phone,   -- ignores NULL phone values
    COUNT(DISTINCT city)  AS distinct_cities    -- counts unique non-NULL cities
FROM student;

COUNT(*) counts rows. COUNT(column) counts rows where that column is not NULL. COUNT(DISTINCT column) counts unique non-NULL values. Mixing these up gives wrong totals.

HAVING: filtering groups

WHERE filters individual rows before grouping. To filter the groups themselves — based on an aggregate — you use HAVING, which runs after GROUP BY:

-- Cities with more than 5 students
SELECT city, COUNT(*) AS student_count
FROM student
GROUP BY city
HAVING COUNT(*) > 5;

You cannot put COUNT(*) > 5 in WHERE — at that stage the groups do not exist yet. This is the single most common GROUP BY confusion.

Combine WHERE and HAVING in one query — WHERE thins the rows first, then groups, then HAVING thins the groups:

-- Among adult students, show cities with more than 5 of them
SELECT city, COUNT(*) AS adult_count
FROM student
WHERE age >= 18           -- filter rows first
GROUP BY city
HAVING COUNT(*) > 5       -- then filter groups
ORDER BY adult_count DESC;

The golden rule of GROUP BY

In a query with GROUP BY, every column in the SELECT list must either be:

1. listed in the GROUP BY clause, or
2. wrapped in an aggregate function.

-- CORRECT: city is grouped, COUNT is aggregated
SELECT city, COUNT(*) FROM student GROUP BY city;

-- WRONG in standard SQL: full_name is neither grouped nor aggregated
-- SELECT city, full_name, COUNT(*) FROM student GROUP BY city;

PostgreSQL and SQL Server reject the wrong version outright. MySQL, by default in older configurations, allowed it and returned an arbitrary full_name — a notorious source of silent bugs. Modern MySQL with ONLY_FULL_GROUP_BY enabled (the default since MySQL 5.7) correctly rejects it too. This is a key vendor difference: write standards-compliant queries and you are safe everywhere.

Aggregates and joins together

Aggregates shine after a join. For example, count enrolments per student:

SELECT s.full_name, COUNT(e.enrolment_id) AS courses_enrolled
FROM student s
LEFT JOIN enrolment e ON s.student_id = e.student_id
GROUP BY s.student_id, s.full_name;   -- group by the key + displayed column

The LEFT JOIN keeps students with zero enrolments; COUNT(e.enrolment_id) correctly returns 0 for them because it ignores the NULLs.

Common mistakes

• Putting an aggregate condition in WHERE. WHERE COUNT(*) > 5 is invalid — use HAVING.
• Selecting an ungrouped, unaggregated column. It fails on standards-compliant databases and returns garbage on lax ones. Group it or aggregate it.
• Using COUNT(column) when you meant COUNT(*). If the column has NULLs, you will undercount. Use COUNT(*) to count rows.
• Forgetting that aggregates ignore NULL. AVG, SUM, etc. skip NULLs, which may or may not be what you want. Decide deliberately.
• Grouping by a different column than you display. Group by the key and include the displayed columns in GROUP BY to stay portable.

FAQ

When do I use HAVING vs WHERE? WHERE filters rows before aggregation; HAVING filters groups after. If your condition uses an aggregate (COUNT, SUM...), it belongs in HAVING.

Can I use an alias in GROUP BY? PostgreSQL and MySQL allow grouping by a SELECT alias as an extension, but the SQL standard does not. For portability, repeat the expression.

Does GROUP BY sort the result? Not reliably. Some databases return grouped output in an incidental order, but you must add ORDER BY if you need a guaranteed order.

Keep learning

Aggregation builds on SQL SELECT basics and pairs with SQL joins. For per-group filtering using a separate query, see subqueries in SQL.

Want to turn raw tables into the reports stakeholders ask for? Join the waitlist for the DBMS & SQL course at Infoplanet, Jalgaon.