Intermediate9 min read PostgreSQL

GROUP BY & HAVING — Advanced Aggregation

Go beyond basic COUNT — combine multiple aggregates, filter groups with HAVING, and use CASE inside GROUP BY for institutional reports.

GROUP BY
HAVING
COUNT
SUM
AVG
aggregation

Want the one-page version first? Read SQL HAVING vs WHERE, then come back here for the full lesson.

Use this when you need to:

Build any roll-up dashboard — totals per region, headcount per department, retention per cohort
Filter on the aggregate itself with HAVING (filter rows with WHERE before grouping; filter groups with HAVING after)
Combine multiple aggregates in one query — total + average + max all from the same GROUP BY
Use FILTER (WHERE ...) inside an aggregate for conditional counts, e.g. active users per month

GROUP BY collapses rows that share the same value into a single summary row, letting aggregate functions (COUNT, SUM, AVG, MIN, MAX) operate per group. HAVING is the WHERE clause for groups — it filters after aggregation, while WHERE filters before.

SQL execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. This means WHERE runs before any grouping, so you cannot use aggregate functions inside WHERE — use HAVING instead.

WHERE vs HAVING

-- WHERE: filter rows BEFORE grouping
SELECT stpr_dept, COUNT(*) AS headcount
FROM ods_stu_acad_levels
WHERE stpr_status = 'A'         -- active students only; drops graduates first
GROUP BY stpr_dept;

-- HAVING: filter groups AFTER aggregation
SELECT stpr_dept, COUNT(*) AS headcount
FROM ods_stu_acad_levels
WHERE stpr_status = 'A'
GROUP BY stpr_dept
HAVING COUNT(*) >= 10;          -- only departments with 10+ active students

Multiple Aggregates in One Query

SELECT
    subject_desc                    AS subject,
    COUNT(*)                        AS section_count,
    SUM(sec_capacity)               AS total_seats,
    ROUND(AVG(sec_capacity), 1)     AS avg_size,
    MIN(sec_capacity)               AS smallest,
    MAX(sec_capacity)               AS largest
FROM ods_course_sections
WHERE sec_term = '2024FA'
GROUP BY subject_desc
ORDER BY section_count DESC, subject;

HAVING with Multiple Conditions

-- Subjects with more than 3 sections AND average capacity above 25
SELECT
    subject_desc                    AS subject,
    COUNT(*)                        AS sections,
    ROUND(AVG(sec_capacity), 1)     AS avg_capacity
FROM ods_course_sections
WHERE sec_term = '2024FA'
GROUP BY subject_desc
HAVING COUNT(*) > 3
   AND AVG(sec_capacity) > 25
ORDER BY avg_capacity DESC;

CASE Inside GROUP BY

You can group by a CASE expression to create custom categories on the fly — without adding a column to the table.

-- Classify students by credit load, then count each category
SELECT
    CASE
        WHEN total_credits >= 12 THEN 'Full-Time'
        WHEN total_credits >= 6  THEN 'Half-Time'
        ELSE                          'Less Than Half'
    END             AS load_category,
    COUNT(*)        AS student_count
FROM (
    SELECT scs_student, SUM(scs_credits) AS total_credits
    FROM ods_stu_course_sec
    WHERE scs_term = '2026SP'
      AND scs_status = 'A'          -- active registrations only
    GROUP BY scs_student
) credit_totals
GROUP BY load_category
ORDER BY student_count DESC;

Common Mistakes

-- ERROR: non-aggregated column not in GROUP BY
SELECT residence_state, last_name, COUNT(*) FROM ods_person GROUP BY residence_state;
-- Fix: add last_name to GROUP BY, or remove it from SELECT

-- ERROR: using aggregate in WHERE
SELECT residence_state FROM ods_person WHERE COUNT(*) > 10 GROUP BY residence_state;
-- Fix: use HAVING COUNT(*) > 10

-- Easy to miss: COUNT(col) vs COUNT(DISTINCT col)
SELECT COUNT(scs_student)          AS registrations,    -- 1980: counts all rows
       COUNT(DISTINCT scs_student) AS unique_students   -- 150: counts unique
FROM ods_stu_course_sec;

Practice challenge

Using ods_stu_course_sec and ods_course_sections, return each subject (subject_desc), its distinct student count, and the total credit hours taken in the 2025FA term. Only show subjects with total credit hours > 100. Sort by total_credit_hours DESC.

Solution

SELECT
    cs.subject_desc                  AS subject,
    COUNT(DISTINCT scs.scs_student)  AS distinct_students,
    SUM(scs.scs_credits)             AS total_credit_hours
FROM ods_stu_course_sec scs
LEFT JOIN ods_course_sections cs ON cs.course_sections_id = scs.scs_course_section
WHERE scs.scs_term = '2025FA'
  AND scs.scs_status = 'A'
GROUP BY cs.subject_desc
HAVING SUM(scs.scs_credits) > 100
ORDER BY total_credit_hours DESC;

Practise this on real university data

Write real PostgreSQL in your browser and get it graded on the rows it returns. No signup and nothing to install for your first query.

Run your first query Browse practice problems

See all 21 lessons