CodeRang
SQL topics

SQL GROUP BY: grouping data with HAVING and aggregates

SQL GROUP BY combines rows into groups so you can calculate values such as counts, sums, averages, minimums, and maximums for each group. The example below shows grouping by store, conditional aggregates, and filtering grouped results with HAVING.

Updated · SQL Arena team

SQL from zero → Analyst trackAll problems

What you will practice

  • COUNT, SUM, AVG, MIN, and MAX
  • grouping by identifiers and attributes
  • WHERE before aggregation and HAVING after it
  • COUNT(*) vs COUNT(column) vs COUNT(DISTINCT)
  • conditional aggregation with FILTER and CASE

A practice problem in the trainer

This is how a problem looks in SQL Arena: the task, the editor, the result and the database schema.

PrevNextRandom
TaskSubmissionsAI Mentor
Find stores with at least two paid orders

You have stores and their orders. Calculate metrics using paid orders only, and keep stores that have at least two paid orders. Return the store name as store_name, the number of paid orders as paid_orders, and their total amount as paid_revenue.

Query 1PostgreSQLv16
SELECT s.store_name,
       COUNT(*) FILTER (WHERE o.status = 'paid') AS paid_orders,
       SUM(o.amount) FILTER (WHERE o.status = 'paid') AS paid_revenue
FROM stores AS s
JOIN orders AS o ON o.store_id = s.id
GROUP BY s.id, s.store_name
HAVING COUNT(*) FILTER (WHERE o.status = 'paid') >= 2
ORDER BY paid_revenue DESC, s.store_name;
Submit
Resultstoresorders
Answer accepted

How SQL GROUP BY works with aggregate functions

After the JOIN, each row represents one order together with its store. GROUP BY s.id, s.store_name then collects those rows into one group per store. Grouping by the id matters because names are not guaranteed to be unique identifiers. The name is also listed in GROUP BY because it appears in SELECT without an aggregate function.

Both aggregates use FILTER (WHERE o.status = 'paid'). Cancelled orders still exist in the store's underlying group, but they do not contribute to either metric. COUNT(*) FILTER (...) counts paid rows, while SUM(o.amount) FILTER (...) adds their amounts. PostgreSQL's FILTER syntax is especially useful when one report needs several conditional aggregates, because each aggregate can state its own condition directly.

HAVING COUNT(*) FILTER (...) >= 2 runs after grouping and removes stores whose aggregate does not meet the requirement. Paper Moon has three paid orders totaling 350, Blue Shelf has two totaling 270, and North Books has two totaling 200. Ink House has only one paid order, while Page Corner has none. ORDER BY paid_revenue DESC, s.store_name sorts the qualifying groups by revenue and provides a deterministic secondary order for ties.

SQLPostgreSQL v16
SELECT s.store_name,
       COUNT(*) FILTER (WHERE o.status = 'paid') AS paid_orders,
       SUM(o.amount) FILTER (WHERE o.status = 'paid') AS paid_revenue
FROM stores AS s
JOIN orders AS o ON o.store_id = s.id
GROUP BY s.id, s.store_name
HAVING COUNT(*) FILTER (WHERE o.status = 'paid') >= 2
ORDER BY paid_revenue DESC, s.store_name;
Query result
store_namepaid_orderspaid_revenue
Paper Moon3350
Blue Shelf2270
North Books2200

Common SQL GROUP BY and HAVING mistakes

01

Putting an aggregate condition in WHERE

WHERE runs before groups and aggregate values exist, so it is not the place for a condition such as COUNT(*) >= 2. Use WHERE for row-level filters and HAVING for conditions based on COUNT, SUM, AVG, or another aggregate.

02

Filtering rows when you need conditional metrics

A WHERE status = 'paid' filter would happen to work for this exact output, but it removes cancelled orders before aggregation. As soon as a report needs paid and cancelled counts side by side, that approach fails. FILTER or SUM(CASE ...) keeps the full group available while calculating each metric selectively.

03

Treating every form of COUNT as equivalent

COUNT(*) counts rows, COUNT(column) skips NULL values in that column, and COUNT(DISTINCT column) counts distinct non-null values. They can produce different answers on the same group, so the correct expression depends on the exact question being asked.

Practice problems on this topic

SQL from zero → Analyst track

Good SQL aggregate-functions practice should change the shape of the question, not just the table names. SQL Arena includes Easy tasks where you identify activity patterns, collect each user's products, or count distinct products, followed by harder work involving platform audiences, event histories, and monthly revenue. Those exercises force you to choose between COUNT, COUNT DISTINCT, SUM, conditional aggregates, and different grouping levels. SQL Arena has 1,192 automatically checked tasks, and every task has its own tables. Submissions are checked against hidden data, so a query that only happens to work for the visible rows is not enough. PostgreSQL, MySQL, ClickHouse, and BigQuery each run in a separate browser sandbox with no installation required. The free plan needs no card and includes 292 tasks across all four dialects. For a structured progression, continue with the “SQL from Scratch → Analyst” track, which moves from basic queries into increasingly demanding aggregation problems.

Reference on this topic

In-depth articles with syntax and examples in the SQL Arena reference.

Frequently asked questions

What is SQL GROUP BY?

SQL GROUP BY combines rows that share the same grouping values. It is commonly used with aggregate functions such as COUNT, SUM, AVG, MIN, and MAX to produce one result row per group.

How do you use SQL GROUP BY?

List the grouping columns after GROUP BY and place aggregate calculations in SELECT. A selected column that is not aggregated generally needs to be included in the grouping columns.

Can SQL GROUP BY use multiple columns?

Yes. Grouping by multiple columns creates a separate group for each distinct combination of their values. This is useful when one column alone does not uniquely describe the level you want to aggregate.

What is the difference between GROUP BY and HAVING in SQL?

GROUP BY defines how rows are divided into groups. HAVING filters those groups after aggregation, so it can use conditions involving COUNT, SUM, and other aggregate results.

What is the difference between WHERE and HAVING with SQL GROUP BY?

WHERE removes source rows before grouping, while HAVING removes groups after aggregation. Use WHERE for row-level conditions and HAVING for conditions based on grouped results.

See also

Practice SQL GROUP BY with real queries

SQL Arena provides aggregate-function exercises across PostgreSQL, MySQL, ClickHouse, and BigQuery, with automatic checking on hidden data. ER diagrams and EXPLAIN are available in the editor. Interview mode uses a server-side timer, removes hints during the session, and provides a report afterward; one session per day is available for free.

Practice GROUP BY

More practice from CodeRang