GROUP BY, HAVING and population stages

sqlrelational-datasqlitepostgresqlindependent-practice

Saved on this device

Full lesson · 4 learning steps Open any exercise for focused practice

Step 1 of 4 · intro card

GROUP BY, HAVING and population stages

Concept and contract

Filter rows before grouping and groups afterward, without changing the intended denominator.

GROUP BY partitions qualifying rows by chosen expressions and produces one summary row per group. The worked query considers active roles, groups them by vacancy country and retains countries with at least two matching roles. It returns UA and US with two each.

The active predicate belongs to the row population before grouping. HAVING evaluates the aggregate condition for each completed group. Mixing those responsibilities can either fail syntactically or quietly change which records and groups the report represents.

Mechanism and worked example

For each active country, write the contributing identities first: CA has 8, IL has 6, UA has 1 and 2, and US has 3 and 4.

For each active country, write the contributing identities first: CA has 8, IL has 6, UA has 1 and 2, and US has 3 and 4. Counting those groups gives one, one, two and two before HAVING removes the smaller groups.

A selected expression must be grouped or aggregated under the portable rules used here. Do not select an unrelated title and assume the engine picks a meaningful representative. Permissive behavior in one engine is not a substitute for a declared grouped contract.

Complete worked example

sql

SELECT country, COUNT(*) AS roles FROM roles WHERE active = 1
GROUP BY country HAVING COUNT(*) >= 2 ORDER BY country;

Reasoning: UA and US each have two active roles before the group threshold is applied.

Counterexamples and common mistakes

The counterexample puts COUNT(*) in WHERE before groups exist.

The counterexample puts COUNT(*) in WHERE before groups exist. Both teaching engines reject that use. A subtler mistake filters salary IS NOT NULL before counting roles, thereby turning the country population into known-salary roles and removing US from a two-role threshold.

Choose whether the report counts roles or observations. If both are needed, return COUNT(*) and COUNT(salary) separately. Do not change the population merely to make an aggregate expression convenient, and do not use HAVING as an unexplained repair for a wrong join.

Counterexample: a plausible but wrong query

sql

SELECT country, COUNT(*) AS roles FROM roles
WHERE active = 1 AND COUNT(*) >= 2 GROUP BY country ORDER BY country;

Reasoning: WHERE cannot use this aggregate before the groups exist.

Independent practice: small query

Find companies with at least two active roles, returning company identity and active-role count.

Find companies with at least two active roles, returning company identity and active-role count. Predict A with two and B with three. Start from companies and match active roles in ON, then group by company identity before applying HAVING.

Use COUNT(r.id) rather than COUNT(*) because an unmatched left join contributes a placeholder. The threshold would remove the empty company either way here, but choosing the correct measure keeps the expression sound when reused in a report that shows zero-count companies.

Step 1: Match active roles to companies. Step 2: Group by company key and require at least two matches.

Practice starter: solve before reading the solution

sql

SELECT c.id, COUNT(r.id) FROM companies c
LEFT JOIN roles r ON r.company_id=c.id AND r.active=1
GROUP BY c.id HAVING /* minimum active count */ ORDER BY c.id;

Reasoning: Count nullable right-side identities, then apply the threshold.

Hint 1: The role identity is null for an unmatched company.
Hint 2: COUNT(r.id) gives that company zero.

Solution, tests and trade-offs

The solution returns A 2 and B 3.

The solution returns A 2 and B 3. C has one active role and D has zero. An independent check should also inspect those excluded groups during development, because a correct-looking final pair might otherwise hide an incorrect zero-count measure.

This is a useful case where a test must examine intermediate reasoning, not only a convenient final count. The larger capstone retains all companies, so the distinction between counting right-side identities and counting placeholder rows becomes directly visible in the final output.

Practice solution and acceptance checks

sql

SELECT c.id, COUNT(r.id) FROM companies c
LEFT JOIN roles r ON r.company_id=c.id AND r.active=1
GROUP BY c.id HAVING COUNT(r.id) >= 2 ORDER BY c.id;

Reasoning: Only A with two and B with three satisfy the practice contract.

Interview reasoning and next step

When explaining a grouped query, follow its logical population stages: relationship, row predicate, grouping, group predicate, projection and ordering.

When explaining a grouped query, follow its logical population stages: relationship, row predicate, grouping, group predicate, projection and ordering. Do not confuse this reasoning order with a promise about the physical optimizer execution plan. The engine can choose a different safe physical strategy.

The foundation capstone combines these decisions into a small report with skill eligibility and observation coverage. Work one stage at a time and reconcile results against the fixture instead of trying to debug a large aggregate statement as an opaque whole.

Optional stretch — after the core task works: Show all company groups before HAVING and reconcile active counts to six.

Computer practice: https://applydjinn.com/typorithm/sql-lab-20261105.zip. Load the shared setup.sql fixture before running a snippet. The local verifier compares exact ordered row arrays in SQLite and PostgreSQL, keeps SQL values separate from driver bindings and never connects to the production website database.

Filter rows before grouping and groups afterward, without changing the intended denominator.