0Pricing
SQL Academy · Lesson

GROUP BY Single and Multiple Columns

Group rows by one or more columns to produce subtotals and category breakdowns, and read GROUP BY query plans.

GROUP BY Single and Multiple Columns is a free SQL Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What GROUP BY Does

GROUP BY partitions rows into groups by one or more column values, then applies aggregates per group.

Single-Column Group

Total orders per user:

SELECT user_id, COUNT(*) AS order_count, SUM(total) AS revenue
FROM orders
GROUP BY user_id
ORDER BY revenue DESC;

Multi-Column Group

Subgroups by category and country:

SELECT country, category, SUM(total) AS revenue
FROM orders o
JOIN products p ON p.id = o.product_id
JOIN users   u ON u.id = o.user_id
GROUP BY country, category
ORDER BY country, revenue DESC;

Every SELECTed Column Must Be Grouped or Aggregated

Standard SQL rule:

-- WRONG — email is neither grouped nor aggregated:
SELECT user_id, email, COUNT(*)
FROM orders
GROUP BY user_id;     -- ERROR

-- RIGHT — group by both, or use an aggregate:
SELECT user_id, MAX(email) AS email, COUNT(*)
FROM orders
GROUP BY user_id;

GROUP BY by Expression

Group by the result of an expression (e.g. month bucket):

SELECT date_trunc('month', created_at) AS month, SUM(total)
FROM orders
GROUP BY date_trunc('month', created_at)
ORDER BY month;

GROUP BY by Position or Alias

You may group by a SELECT-list position or alias (PostgreSQL):

SELECT date_trunc('month', created_at) AS month, SUM(total)
FROM orders
GROUP BY month             -- alias
ORDER BY 1;                -- position

Grouping Sets, ROLLUP, CUBE

Compute multiple groupings in one query — perfect for sub-totals:

SELECT country, category, SUM(total)
FROM orders o JOIN ...
GROUP BY ROLLUP (country, category);

-- Returns:  per (country, category),  per country,  grand total

Empty Groups Don't Exist

GROUP BY only returns groups that have at least one row. If you want every category (including those with zero orders), LEFT JOIN from the master list and aggregate.

GROUP BY and Indexes

The planner may use a hash aggregate (build hash table of groups) or a sort + group strategy. Indexes on the grouping column can make the second approach fast.

Window Functions vs GROUP BY

GROUP BY collapses rows. Window functions add aggregate columns while keeping every row. Pick the right tool — covered in Window Functions Deep Dive.

Aliases in HAVING vs WHERE

Logical order: FROM → WHERE → GROUP BY → HAVING → SELECT. WHERE cannot see aggregates; HAVING can.

Recap

GROUP BY is the workhorse of reporting.

  • Every SELECTed column must be grouped or aggregated
  • Group by columns, expressions, or aliases
  • Use ROLLUP/CUBE/GROUPING SETS for cross-tabs

Quick Check

In a GROUP BY country query, can the SELECT list include a column city that is neither aggregated nor grouped?

Frequently asked questions

Is the “GROUP BY Single and Multiple Columns” lesson free?

Yes — the full text of “GROUP BY Single and Multiple Columns” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “GROUP BY Single and Multiple Columns”?

Group rows by one or more columns to produce subtotals and category breakdowns, and read GROUP BY query plans. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “GROUP BY Single and Multiple Columns” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. COUNT, SUM, AVG, MIN, MAX
  2. GROUP BY Single and Multiple Columns
  3. HAVING vs WHERE
  4. Common Pitfalls: NULLs in Aggregates
← Back to SQL Academy