SQL GROUP BY and HAVING: count learners by city

Choose the unit represented by one result row

A normal student query returns one row for each matching student. A grouped query can return one row for each city instead. Before writing GROUP BY, finish this sentence: one result row represents one city. That choice tells you which columns belong in the group key and which values must be calculated from the group's member rows.

In the sample data Jaipur contains three students and the other recorded cities have one each. One student has a missing city. SQL places missing grouping values together in a group; it does not invent a real city name for them. Decide separately whether that unknown-location group belongs in a particular report.

Filter rows before groups and groups after aggregation

WHERE filters source rows before they contribute to an aggregate. HAVING filters the resulting groups. To keep cities with at least two students, use HAVING COUNT(*) >= 2. Putting COUNT(*) in this query's WHERE clause is not the right phase for that condition. If the requirement first restricts passing students, apply that row rule in WHERE before grouping.

COUNT(*) counts rows, and AVG(score) calculates the average of non-null scores. The setup makes score NOT NULL, so every student contributes a score. ROUND displays the average to one decimal place here. Display rounding does not change the underlying stored marks, nor does it replace a defined rounding policy for another application.

Keep selected columns meaningful for the group

Select the grouping key and aggregates that describe the group. Adding an ungrouped name column does not clearly identify a particular student in that city. SQLite permits some bare-column aggregate queries, but relying on that extension can produce an answer different from the question you meant to ask. Other databases may reject such a query.

Validate a grouped result by calculating one group manually. For Jaipur, 82 + 94 + 40 is 216 and 216 divided by 3 is 72. Then check a case with no qualifying group. A valid aggregate query may return zero rows, which is different from returning a made-up group with a count of zero.

Run the worked example

PRAGMA foreign_keys = ON;
CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    city TEXT,
    score INTEGER NOT NULL CHECK (score BETWEEN 0 AND 100),
    cohort TEXT NOT NULL
);
CREATE TABLE courses (id INTEGER PRIMARY KEY, title TEXT NOT NULL UNIQUE);
CREATE TABLE enrollments (
    student_id INTEGER NOT NULL REFERENCES students(id),
    course_id INTEGER NOT NULL REFERENCES courses(id),
    PRIMARY KEY (student_id, course_id)
);
INSERT INTO students VALUES
    (1, 'Asha', 'Jaipur', 82, 'Java'),
    (2, 'Aman', 'Ajmer', 66, 'Java'),
    (3, 'Jaya', 'Jaipur', 94, 'SQL'),
    (4, 'Kiran', NULL, 48, 'SQL'),
    (5, 'Neha', 'Kota', 82, 'SQL'),
    (6, 'Kabir', 'Jaipur', 40, 'Java');
INSERT INTO courses VALUES (10, 'Java'), (20, 'SQL'), (30, 'Python');
INSERT INTO enrollments VALUES (1, 10), (1, 20), (2, 10), (3, 20), (5, 20);

Download setup.sql

SELECT city, COUNT(*) AS learners, ROUND(AVG(score), 1) AS average_score
FROM students
GROUP BY city
HAVING COUNT(*) >= 2
ORDER BY city;

Expected result

1. GROUP BY collects the three Jaipur records together.
2. HAVING removes each one-student group, including the missing-city group.
3. The average is computed from all three Jaipur marks, including 40.

Try it yourself

Count learners in each cohort, keeping every cohort. Return cohort and learners, ordered alphabetically by cohort.

SELECT cohort, COUNT(*) AS learners
FROM students GROUP BY cohort ORDER BY cohort;

Quick check

Choose an answer, then check the explanation. Your answer stays in this page and is not saved as a student record.

Keep learning

Explain the result in your own words, change one input and predict the new output. For additional structured assignments, explore the workbook sample before requesting the full pack.