SQL GROUP BY Multiple Columns

How multi-column grouping actually works

When you write GROUP BY a, b, PostgreSQL treats the pair (a, b) as a composite key. Every distinct combination of values becomes one group. On this dataset Fall 2025 has 30 accepted applications and 10 deposited ones — those are two groups, not one, even though they share a term.

Order in the GROUP BY clause does not change the result, only how you read it. GROUP BY term, status and GROUP BY status, term produce the same rows; they differ only in which column you would naturally sort by next. The grouping logic is set-based, not ordered.

One dataset-specific habit is baked into the example: it joins ods_terms and sorts on term_start_date. Term codes like 2026FA and 2026SP sort alphabetically, and alphabetically Fall comes before Spring — the opposite of the calendar. Grouping by a term code is fine; ordering by one is a silent bug.

-- Application volume by start term AND status
SELECT
  t.term_desc,
  a.current_status_desc AS status,
  COUNT(*)              AS applications
FROM ods_applications a
JOIN ods_terms t ON t.terms_id = a.appl_start_term
GROUP BY t.term_desc, t.term_start_date, a.current_status_desc
ORDER BY t.term_start_date, status;

The SELECT-list rule

Every non-aggregate column you put in the SELECT list must also appear in the GROUP BY clause. This catches every beginner once. If you select appl_start_term, current_status_desc, appl_acad_program_title but only group by the first two, PostgreSQL has no way to pick which program title to return for the group — it errors with column "ods_applications.appl_acad_program_title" must appear in the GROUP BY clause or be used in an aggregate function.

The fix is one of three things. Add the column to GROUP BY (creating finer groups), wrap it in an aggregate (MAX(appl_acad_program_title), STRING_AGG(appl_acad_program_title, ', ')), or remove it from the SELECT list. The right choice depends on what you actually want to see.

A useful mental model: every row in the result is a single group. Each output column has to either describe the group (a grouping column) or summarize the rows in the group (an aggregate). There is no third option.

-- Wrong: appl_acad_program_title is neither grouped nor aggregated
SELECT appl_start_term, current_status_desc, appl_acad_program_title, COUNT(*)
FROM ods_applications
GROUP BY appl_start_term, current_status_desc;
-- ERROR: column "ods_applications.appl_acad_program_title" must appear
--        in the GROUP BY clause or be used in an aggregate function

-- Right: aggregate the extra column
SELECT
  appl_start_term,
  current_status_desc                                AS status,
  STRING_AGG(DISTINCT appl_acad_program_title, ', ') AS programs,
  COUNT(*)                                           AS applications
FROM ods_applications
GROUP BY appl_start_term, current_status_desc;

Pivot-style breakdowns with conditional aggregation

Multi-column GROUP BY produces a long-and-narrow result: one row per combination, one column for the count. For reporting, you often want it wide instead — one row per primary dimension, one column per category of the secondary dimension. This is a pivot.

PostgreSQL gives you two clean ways to pivot in a single GROUP BY: COUNT(*) FILTER (WHERE ...) and SUM(CASE WHEN ... THEN 1 ELSE 0 END). Both compute a count restricted to rows that match a condition. FILTER is more readable; CASE is more portable. The result is the same.

The example below pivots application status: instead of one row per (term, status), you get one row per term with separate columns for accepted, deposited, and denied counts (appl_current_status codes AC, DP, DN). This is the shape you want for a dashboard, an executive summary, or a CSV export.

-- Long form: one row per (term, status)
SELECT appl_start_term, appl_current_status, COUNT(*) AS n
FROM ods_applications
GROUP BY appl_start_term, appl_current_status;

-- Pivoted: one row per term, a column per status
SELECT
  t.term_desc,
  COUNT(*) FILTER (WHERE a.appl_current_status = 'AC') AS accepted,
  COUNT(*) FILTER (WHERE a.appl_current_status = 'DP') AS deposited,
  COUNT(*) FILTER (WHERE a.appl_current_status = 'DN') AS denied,
  COUNT(*)                                             AS total
FROM ods_applications a
JOIN ods_terms t ON t.terms_id = a.appl_start_term
GROUP BY t.term_desc, t.term_start_date
ORDER BY t.term_start_date;

NULLs and empty groups

NULL is its own group in GROUP BY. On ods_stu_course_sec, every Fall 2026 registration is still in progress, so scs_final_grade is NULL on all 444 of them — and they form a single group with NULL as the grouping value. This is different from how NULL behaves in equality (scs_final_grade = NULL is never true) and trips people up — you cannot make NULLs disappear by accident; only an explicit WHERE scs_final_grade IS NOT NULL removes them.

Multiple grouping columns multiply this effect. A (term, grade) group where the grade is NULL is distinct from one where it is 'A'. If you want to fold NULLs into a sensible label, use COALESCE — and put the same COALESCE expression in the GROUP BY, or the SELECT-list rule will bite.

There is no group for combinations that have zero rows. If no Fall 2025 applications were denied, you will not see a Fall 2025 / Denied row in the output. To force every combination to appear (with zero where missing), generate the cross product separately and LEFT JOIN — a more advanced pattern worth knowing.

-- Registrations per term and final grade, with in-progress
-- registrations labelled instead of showing NULL
SELECT
  t.term_desc,
  COALESCE(s.scs_final_grade, 'in progress') AS final_grade,
  COUNT(*)                                   AS registrations
FROM ods_stu_course_sec s
JOIN ods_terms t ON t.terms_id = s.scs_term
GROUP BY t.term_desc, t.term_start_date, COALESCE(s.scs_final_grade, 'in progress')
ORDER BY t.term_start_date, final_grade;

Practice on real data

Multi-column GROUP BY is one of the highest-leverage patterns in SQL — it is what turns a row-by-row data set into a report. The fastest way to internalize it is to write the same shape of query against five or six different real-world questions until the SELECT-list rule stops feeling arbitrary and the pivot pattern feels obvious.

QueryU has a focused set of medium-difficulty challenges built specifically for this. Each runs against the same university dataset as the examples above, grades by result-set comparison (any valid SQL that produces the right rows passes), and gives you immediate feedback. The first 31 challenges are free, and the home-page demo needs no signup at all.

-- The shape of M01, "Admissions Funnel", from the medium tier:
-- for each start term, count applications by status.
SELECT
  a.appl_start_term,
  a.current_status_desc,
  COUNT(*) AS applicant_count
FROM ods_applications a
JOIN ods_terms t ON t.terms_id = a.appl_start_term
GROUP BY a.appl_start_term, t.term_start_date, a.current_status_desc
ORDER BY t.term_start_date, applicant_count DESC;

Practice these on QueryU

Each challenge below tests the patterns above on the same university data. Click through to the editor — you can run, get graded, and see the expected result set immediately.