SQL HAVING vs WHERE

The rule: WHERE first, then GROUP BY, then HAVING

SQL evaluates a SELECT in a fixed logical order. FROM is first, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY. HAVING runs after grouping, which is why aggregate functions like COUNT and SUM are visible to it. WHERE runs before grouping, so those aggregates have not been computed yet — referencing them there is a syntax error, not a subtle bug.

So the short rule is: if your filter uses an aggregate, it has to be HAVING. Everything else can and should be WHERE. "Departments with at least 10 students" — HAVING (needs COUNT). "Only currently active students" — WHERE (a per-row property, stpr_status = 'A').

You will occasionally see HAVING without a GROUP BY. That works in PostgreSQL and treats the whole table as one group. It is unusual and usually a code-smell — a scalar subquery or a plain WHERE is normally clearer.

-- Which departments have at least 10 currently active students?
-- (stpr_status: A = Active, G = Graduated)
SELECT
  d.dept_desc,
  COUNT(*) AS active_students
FROM ods_stu_acad_levels l
JOIN ods_departments d ON d.departments_id = l.stpr_dept
WHERE l.stpr_status = 'A'        -- per-row filter, runs first
GROUP BY d.dept_desc
HAVING COUNT(*) >= 10            -- per-group filter, runs after
ORDER BY active_students DESC;

-- dept_desc         active_students
-- Business          20
-- Computer Science  12

Prefer WHERE when you have the choice

For non-aggregate filters, WHERE is almost always the better call. WHERE fires before the group-and-aggregate pass, so the aggregation runs over a smaller set of rows, and on indexed columns the planner can use the index to skip whole ranges of the table.

A common anti-pattern is a query that groups everything, then uses HAVING to throw most of it away. Read literally, the first example below builds all 17 department groups and keeps two. PostgreSQL's planner is smart enough to push a HAVING condition that contains no aggregate down into WHERE for you, so here the two run the same; not every engine does that, and it stops working once the query grows a subquery or a window function. If the same predicate could go in either clause, put it in WHERE, where it says what you mean.

The one time HAVING beats WHERE for a non-aggregate is when you are filtering on a value computed by GROUP BY itself — for example, the grouping key after a CASE-WHEN bucket. That is rare in practice; when it comes up, WHERE inside a CTE is usually cleaner still.

-- As written: group every department, then throw most of them away
SELECT stpr_dept, COUNT(*) AS n
FROM ods_stu_acad_levels
GROUP BY stpr_dept
HAVING stpr_dept IN ('CSCI', 'MATH');

-- Clearer: filter first, aggregate over what is left
SELECT stpr_dept, COUNT(*) AS n
FROM ods_stu_acad_levels
WHERE stpr_dept IN ('CSCI', 'MATH')
GROUP BY stpr_dept;

-- Both return:  CSCI 16 | MATH 7

Combine WHERE and HAVING in the same query

It is normal for one query to use both clauses. WHERE trims which rows enter the aggregation; HAVING trims which groups survive. The two answer different questions and often need to run together.

Think of it as a two-stage funnel: WHERE narrows the population, GROUP BY collapses the survivors into buckets, HAVING keeps only the buckets that meet the aggregate condition. Any part of your report logic can live in either stage — put each condition where it naturally belongs.

A useful reading trick: rewrite the query in English clause-by-clause. "Of currently active students with a GPA on file (WHERE), grouped by program (GROUP BY), which programs average 3.0 or better with at least five students (HAVING)?" If that sentence reads cleanly, the SQL usually does too.

-- Both clauses working together
SELECT
  p.acpg_title              AS program,
  COUNT(*)                  AS enrolled_count,
  ROUND(AVG(l.stpr_gpa), 2) AS avg_gpa
FROM ods_stu_acad_levels l
JOIN ods_acad_programs p ON p.acad_programs_id = l.sttr_acad_program
WHERE l.stpr_status = 'A'          -- narrow the population
  AND l.stpr_gpa IS NOT NULL
GROUP BY p.acpg_title
HAVING AVG(l.stpr_gpa) >= 3.0      -- keep only strong-GPA programs
   AND COUNT(*) >= 5               -- with enough students to be meaningful
ORDER BY avg_gpa DESC;

-- Chemistry 6 3.30 | Marketing 6 3.20 | Criminal Justice 5 3.15 | ...

Two silent-bug traps

Trap one: WHERE cannot see SELECT aliases. Because WHERE runs before SELECT, an alias defined in SELECT does not yet exist. "WHERE course_load > 4" against a SELECT that defines course_load will fail. HAVING cannot see them in PostgreSQL either: "HAVING course_load > 4" fails with the same "column does not exist" error, so repeat the aggregate instead (MySQL is the engine that allows the alias there). ORDER BY is the one clause that can use a SELECT alias, because it runs after SELECT.

Trap two: aggregate-on-NULL semantics. COUNT(*) counts every row including NULL, but COUNT(col) counts only non-NULL values. So "HAVING COUNT(stpr_gpa) > 0" filters out groups where every GPA is NULL — which is often what you want, but is not the same as "HAVING NOT NULL." Being explicit about which COUNT you mean prevents whole cohorts from silently disappearing.

Related: a WHERE that removes NULLs before aggregation changes the answer of AVG, MIN, and MAX. If a report unexpectedly does not match a source-of-truth number, check whether a WHERE upstream is quietly discarding rows the aggregate would otherwise have seen.

-- WRONG: alias 'course_load' does not exist yet when WHERE runs
SELECT
  scs_student,
  scs_term,
  COUNT(*) AS course_load
FROM ods_stu_course_sec
WHERE course_load > 4         -- ERROR: column "course_load" does not exist
GROUP BY scs_student, scs_term;

-- RIGHT: filter the aggregate in HAVING
SELECT
  scs_student,
  scs_term,
  COUNT(*) AS course_load
FROM ods_stu_course_sec
GROUP BY scs_student, scs_term
HAVING COUNT(*) > 4
ORDER BY course_load DESC;

Practice these on QueryU

Each challenge below exercises the HAVING vs WHERE decision on the same dataset — the kind of report an institutional-research team actually runs. Grading compares the rows you return, so any correct query passes.