SQL GROUP BY groups rows sharing one or more values, enabling calculations such as sales per region, orders per customer, or mean salary per department. The challenge is choosing the correct grain, row filters and join relationships before computing aggregates. A syntactically valid query can still report wrong business totals.

These are original teaching examples in PostgreSQL SQL syntax. Basic aggregation also applies to SQL Server and other relational databases, but date functions, aliases and some extensions vary by engine. Do not copy cross-dialect snippets without checking the relevant database documentation.

Sample data for every example

CREATE TABLE sales (
  order_id INTEGER PRIMARY KEY,
  region VARCHAR(40),
  product VARCHAR(40),
  amount NUMERIC(12,2),
  sold_at DATE
);
INSERT INTO sales VALUES
(1,'North','Pen',20,'2026-01-05'),
(2,'North','Book',80,'2026-01-06'),
(3,'South','Pen',40,'2026-01-07'),
(4,'South','Book',120,'2026-02-01'),
(5,'North','Pen',30,'2026-02-02');

The synthetic table has five orders totaling 290 units of one fictional currency. Region totals are North 130 and South 160. January revenue is 140; February is 150. These simple values help check every query independently, without relying on an answer key.

Basic GROUP BY, WHERE, HAVING and ORDER BY

SELECT region, SUM(amount) AS revenue
FROM sales
WHERE amount IS NOT NULL
GROUP BY region
HAVING SUM(amount) > 100
ORDER BY revenue DESC;

WHERE filters input rows before grouping, GROUP BY combines rows with equal grouping keys, HAVING filters groups after aggregation, and ORDER BY sorts the final result. This example returns South 160 before North 130. GROUP BY alone never promises sorted output. To exclude groups with only one order use HAVING COUNT(*) > 1, not WHERE COUNT(*) > 1.

Every ordinary expression in the selected columns must generally appear in GROUP BY or be aggregated. Some engines recognise functional dependencies, such as primary-key grouping in PostgreSQL, but explicit grouping is easier to transfer between SQL systems.

Aggregate functions and NULL behavior

Without GROUP BY, SELECT SUM(amount) FROM sales; computes one overall summary, returning 290. For a grouped result, SELECT region, COUNT(*) FROM sales GROUP BY region; returns North 3 and South 2. Grouping columns containing NULL typically place all NULL grouping values together; distinguish missing regions from actual named regions.

Multiple grouping columns

SELECT region, product, SUM(amount) AS revenue
FROM sales
GROUP BY region, product
ORDER BY region, product;

Four groups result: North/Book 80; North/Pen 50; South/Book 120; South/Pen 40. Adding product to region produces more granular results. It does not automatically create a subtotal row for North or South: advanced GROUPING SETS or ROLLUP is required for that.

Filter source rows before summarising

SELECT region, SUM(amount) AS january_revenue
FROM sales
WHERE sold_at >= DATE '2026-01-01'
  AND sold_at < DATE '2026-02-01'
GROUP BY region
HAVING SUM(amount) > 50;

Only North 100 remains, while South January revenue of 40 is excluded by HAVING. A WHERE predicate on a date determines which orders enter the grouped calculation; HAVING determines which finished groups remain. For timestamps, using an end-exclusive period avoids excluding times late on the final date.

Conditional bands and expressions

SELECT
  CASE WHEN amount < 50 THEN 'Small'
       WHEN amount < 100 THEN 'Medium'
       ELSE 'Large' END AS amount_band,
  COUNT(*) AS order_count
FROM sales
GROUP BY
  CASE WHEN amount < 50 THEN 'Small'
       WHEN amount < 100 THEN 'Medium'
       ELSE 'Large' END;

For these non-NULL rows, Small contains three orders, Medium one and Large one. If NULL values were allowed, add WHEN amount IS NULL THEN 'Unknown' before the comparisons. Some SQL dialects permit a SELECT alias in GROUP BY and others do not; repeating the expression or using a CTE avoids relying on dialect-specific alias rules. Conditional sums can also use SUM(CASE WHEN product='Pen' THEN amount ELSE 0 END).

Group calendar periods with the year

SELECT DATE_TRUNC('month', sold_at) AS month_start,
       SUM(amount) AS revenue
FROM sales
GROUP BY DATE_TRUNC('month', sold_at)
ORDER BY month_start;

January 2026 has 140, February 2026 has 150. Grouping solely by EXTRACT(MONTH FROM sold_at) would merge the same month from multiple years. PostgreSQL uses DATE_TRUNC; SQL Server’s date-bucketing functions differ. Check dates, time zones and whether the analysis means calendar or fiscal periods.

GROUP BY after a JOIN: measure the right grain

-- Assumes exactly one product_catalog row per product.
SELECT c.category, SUM(s.amount) AS revenue
FROM sales AS s
JOIN product_catalog AS c ON c.product = s.product
GROUP BY c.category;

product_catalog is an additional assumed table, not created in the five-row example above. A duplicate dimension key can multiply each matching sales row before SUM and inflate revenue. Check join uniqueness, unmatched records and whether COUNT(*) is intended to count joined rows or unique orders. For unique orders, COUNT(DISTINCT s.order_id) may be needed.

Top-selling product in each region

Do not nest MAX(SUM(amount)) in an invalid correlated subquery or assume a derived-table alias containing spaces works unchanged. First aggregate, then rank the grouped totals:

WITH totals AS (
  SELECT region, product, SUM(amount) AS revenue
  FROM sales
  GROUP BY region, product
), ranked AS (
  SELECT region, product, revenue,
         ROW_NUMBER() OVER (
           PARTITION BY region
           ORDER BY revenue DESC, product
         ) AS position
  FROM totals
)
SELECT region, product, revenue
FROM ranked
WHERE position = 1
ORDER BY region;

This yields North/Book 80 and South/Book 120. The alphabetical product tie-breaker makes the result deterministic. To return all tied top products rather than one arbitrarily selected product, use RANK() and filter to rank 1.

ROLLUP, CUBE and GROUPING SETS

SELECT region, product, SUM(amount) AS revenue,
       GROUPING(region) AS region_total_flag,
       GROUPING(product) AS product_total_flag
FROM sales
GROUP BY ROLLUP(region, product);

Subtotal rows contain NULL placeholders that must be distinguished from real NULL source values. GROUPING can mark which value is a subtotal placeholder. PostgreSQL and SQL Server support these constructs; other database products have different support or syntax. Verify the target dialect.

Aggregate versus window functions

GROUP BY reduces multiple rows into one result per group. A window aggregate can preserve original rows while adding a group metric:

SELECT order_id, region, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total
FROM sales
ORDER BY order_id;

Each North order appears with regional total 130, each South order with 160. A window partition is not the same concept as physically partitioning a database table; performance depends on the execution plan and source data.

Performance and query correctness

Database engines may use hash or sort aggregation, and expensive joins, skewed groups, memory spills and distinct-count work can dominate runtime. Use EXPLAIN or the platform’s query plan and measure actual bottlenecks. An index on grouping or filtering columns can help selected workloads but adds write and storage overhead and does not guarantee a faster GROUP BY.

ORDER BY SUM(amount) DESC LIMIT 5 restricts final rows, but it does not guarantee the database avoided reading and aggregating its qualifying input. Grouping on expressions is not inherently forbidden; expression indexes, persisted computed values or query rewrites may help when verified by measurement. Approximate distinct counts are suitable only when the business can accept error bounds, not when an exact financial figure is required.

Common errors and their corrections

Practice with verifiable small examples

1. Count orders by region, then sort by count descending. Expected results: North 3, South 2.
2. Use GROUP BY region, product and HAVING SUM(amount) > 50. Expected groups: North/Book 80 and South/Book 120.
3. Report monthly revenue with full month-year buckets. Expected: January 2026 140, February 2026 150.
4. Add a product dimension with duplicate keys and a new record with NULL amount. Explain which metrics change before running a query.

Final reporting principle

Before writing a SQL summary, define one result row per what: customer, region, product, month or another grain. Then decide which source records count, aggregate only the needed measures, filter group results, and sort explicitly. Test representative totals with a small, known dataset before using the results in a dashboard, audit or business decision.