Introduction

GROUP BY and ORDER BY solve different problems, but they are commonly used in the same report query. GROUP BY decides how rows are collapsed into summary groups, while ORDER BY decides how those summary rows are presented at the end. Once that separation is clear, most confusing SQL errors around aggregation become much easier to diagnose.

Understand the Query Stages First

A grouped query is easier to reason about if you think in stages instead of reading it top to bottom. In broad terms, the database engine first gathers rows, then filters them, then forms groups, then calculates aggregates, and only after that sorts the final result set.

A typical pattern looks like this:

1SELECT
2  customer_id,
3  COUNT(*) AS order_count,
4  SUM(total_amount) AS revenue
5FROM orders
6WHERE status = 'paid'
7GROUP BY customer_id
8ORDER BY revenue DESC, customer_id ASC;

The important consequence is that ORDER BY sees the grouped result, not the original raw rows. That is why sorting by revenue is valid here even though revenue does not exist in the base table. It is an alias produced by aggregation.

Grouping Controls Which Columns Are Legal

The most common GROUP BY mistake is selecting a column that is neither grouped nor aggregated. Once rows are collapsed into one row per group, there is no single value available for any ungrouped column unless you tell the database how to summarize it.

Invalid example:

1SELECT
2  customer_id,
3  created_at,
4  COUNT(*) AS order_count
5FROM orders
6GROUP BY customer_id;

If one customer has ten orders, which created_at value should the engine return. SQL has no deterministic answer unless you specify one.

Valid alternatives are:

1SELECT
2  customer_id,
3  MIN(created_at) AS first_order_at,
4  MAX(created_at) AS last_order_at,
5  COUNT(*) AS order_count
6FROM orders
7GROUP BY customer_id
8ORDER BY last_order_at DESC;

Here the grouped row has well-defined values, so ordering by one of those aggregates is safe.

Order By Aggregates and Aliases

Once grouping is complete, you can order by grouped columns, aggregate expressions, or their aliases depending on the SQL dialect.

1SELECT
2  region,
3  SUM(total_amount) AS revenue
4FROM invoices
5GROUP BY region
6ORDER BY revenue DESC;

You can also write ORDER BY SUM(total_amount) DESC, but the alias is usually easier to read and maintain. In production queries it is also worth adding a tie-breaker so output order remains deterministic when two groups have the same revenue.

1SELECT
2  region,
3  SUM(total_amount) AS revenue
4FROM invoices
5GROUP BY region
6ORDER BY revenue DESC, region ASC;

That small secondary sort saves a lot of confusion in dashboards, exports, and tests.

Use HAVING for Group Filters

Another frequent source of confusion is deciding whether a condition belongs in WHERE or HAVING. WHERE filters individual rows before grouping. HAVING filters whole groups after the aggregates have been calculated.

1SELECT
2  salesperson_id,
3  COUNT(*) AS won_deals,
4  SUM(amount) AS won_amount
5FROM deals
6WHERE status = 'WON'
7GROUP BY salesperson_id
8HAVING COUNT(*) >= 5
9ORDER BY won_amount DESC;

This query first removes non-winning rows, then groups the remaining rows by salesperson, then discards groups with fewer than five wins, and finally orders the remaining summary rows.

A simple rule is:

• use WHERE when the condition can be evaluated per row
• use HAVING when the condition depends on an aggregate

Do Not Abuse GROUP BY for Top Row Per Group

People often try to use GROUP BY when the real problem is “give me the highest-value row for each customer.” That is not a normal aggregation problem, because you need one full row, not a summary of many rows. A window function is usually the correct tool.

1WITH ranked_orders AS (
2  SELECT
3    customer_id,
4    order_id,
5    total_amount,
6    ROW_NUMBER() OVER (
7      PARTITION BY customer_id
8      ORDER BY total_amount DESC, order_id ASC
9    ) AS rn
10  FROM orders
11)
12SELECT
13  customer_id,
14  order_id,
15  total_amount
16FROM ranked_orders
17WHERE rn = 1
18ORDER BY total_amount DESC, customer_id ASC;

This solves a very common reporting request that cannot be expressed cleanly with a plain grouped select.

Performance Still Matters

Grouped queries often end with a sort, and sorts can become expensive on large tables. The main performance levers are not mysterious:

• reduce the input set early with WHERE
• index columns used for filtering and joining
• keep the grouped key set as small as the business question allows
• avoid selecting unnecessary derived values

If the same report runs constantly on very large data, a pre-aggregated summary table may be a better design than asking the database to group raw events every time.

Common Pitfalls

• Selecting columns that are neither part of GROUP BY nor wrapped in an aggregate.
• Assuming ORDER BY sorts raw rows before grouping happens.
• Using HAVING for filters that should have been applied earlier in WHERE.
• Forgetting tie-breaker columns, which produces unstable output order when aggregate values match.
• Trying to fetch a representative detail row with GROUP BY when a window function is the correct approach.

Summary

• 'GROUP BY defines the summary grain of the result set.'
• 'ORDER BY sorts the grouped output after aggregates are computed.'
• Every selected column in a grouped query must be grouped or aggregated.
• 'HAVING filters groups, while WHERE filters source rows.'
• For “top row per group” problems, use window functions instead of forcing GROUP BY to do the wrong job.