SQL GROUP BY and HAVING: Aggregation Made Simple

First decide what one result row means

A sales report starts with a question: do you need one row per invoice, per customer, or per billing country? This is the output grain. GROUP BY collects eligible input rows with matching keys and produces one summary row per group.

Our source contains 412 invoices belonging to 59 customers. A customer summary has 59 rows; each row describes all invoices for one customer. A country summary groups different customers together. Choose the grain before adding totals.

Prerequisite: recognize SELECT, FROM and WHERE. No earlier lesson is required. If your report combines tables, read JOINs vs Subqueries and check row multiplicity before aggregating. You can practice with checked exercises after the examples.

The Chinook sample database

These examples use the complete Chinook music-store database available in the DataTug demo: 59 customers and 412 invoices, with every original table and value retained. All 59 customers have invoices. Queries that focus on one customer or qualifying invoices show that condition explicitly in the SQL.

The relationship is Customer.CustomerId → Invoice.CustomerId: one customer can have many invoices; each invoice belongs to one customer. InvoiceId is the invoice’s unique key. InvoiceDate is ISO-sortable text; Total is a numeric amount in the dataset’s units. This source uses dates from 2021–2025; other Chinook releases differ. Invoice IDs are not necessarily chronological.

Results are shown as a preview for clarity. Tables with more than 10 records initially show the first 10. Choose Show all to reveal every matching record, or Run to execute the exact displayed SQL in the DataTug editor against the same Chinook database. No query is saved to the project. The preview does not add a WHERE or LIMIT clause. Synthetic examples define their own rows with VALUES in the displayed query.

Download the complete Chinook SQLite database and open it with SQLite 3.25.0 or later, then run any query below. No account or network database is required. Query downloads contain every displayed example and exercise solution. Frozen result tables are checked against this database; each code header states its minimum SQLite version.

Chinook upstream and schema · Chinook licence · Source attribution. SQLite uses binary floating-point for many decimal operations; examples round money for display. Production finance needs an explicit exact-decimal or integer-minor-unit policy.

COUNT, SUM and AVG summarize the input

SELECT COUNT(*) AS InvoiceCount, ROUND(SUM(Total), 2) AS Revenue,
       ROUND(AVG(Total), 2) AS AverageInvoice
FROM Invoice;

COUNT(*) counts invoices. SUM(Total) adds their amounts. AVG(Total) calculates the average invoice amount. Without GROUP BY, this aggregate query produces one summary for the entire eligible input. ROUND controls the displayed precision; it does not change which rows enter the calculation.

SELECT CustomerId, ROUND(SUM(Total), 2) AS CustomerTotal
FROM Invoice GROUP BY CustomerId ORDER BY CustomerId;

Adding GROUP BY CustomerId creates a separate sum per customer. Customer 1 totals 39.62 across seven invoices; their summary is one row. GROUP BY does not promise a display order, so every multirow example specifies ORDER BY.

AVG(Total) weights each non-NULL invoice equally. Averaging customer averages instead would weight customers equally, which can produce a different result when invoice counts differ. Define the denominator your question needs.

Grouping keys define which rows belong together

SELECT BillingCountry, COUNT(*) AS InvoiceCount,
       ROUND(SUM(Total), 2) AS Revenue, ROUND(AVG(Total), 2) AS AverageInvoice
FROM Invoice
GROUP BY BillingCountry
ORDER BY BillingCountry;

BillingCountry answers where invoices were billed, rather than the customer's current profile country. Use the field that matches your business question. Select each grouping key or an aggregate; an ungrouped invoice ID beside a country total cannot identify all contributing invoices.

SELECT BillingCountry, BillingCity, COUNT(*) AS InvoiceCount,
       ROUND(SUM(Total), 2) AS Revenue
FROM Invoice
GROUP BY BillingCountry, BillingCity
ORDER BY BillingCountry, BillingCity;

Two keys define one group for each distinct country/city pair. Including the country avoids accidentally combining cities with the same name in different countries. Adding a key makes the grain more detailed; removing one combines groups. For real customer reporting, use a stable CustomerId rather than assuming names are unique.

Grouping depends on the engine's equality and collation rules. Different spellings and letter case can form different groups. Normalize deliberately when the report needs that behavior; do not silently merge business identities.

HAVING keeps groups that meet a condition

SELECT CustomerId, COUNT(*) AS InvoiceCount, ROUND(SUM(Total), 2) AS CustomerTotal
FROM Invoice
GROUP BY CustomerId
HAVING SUM(Total) > 45
ORDER BY CustomerId;

Each customer gets a total, then HAVING keeps only totals greater than 45. The result has five customers. This condition belongs on the aggregate at this SELECT level: WHERE cannot evaluate that same-level SUM before its group exists.

For a fuller comparison—including cases where WHERE and HAVING return identical rows and what that means for cost—continue to WHERE vs HAVING. Repeating SUM(Total) in HAVING also avoids relying on engine-specific alias support.

WHERE chooses the population; HAVING chooses the summaries

SELECT CustomerId, COUNT(*) AS InvoiceCount, ROUND(SUM(Total), 2) AS CustomerTotal
FROM Invoice
WHERE InvoiceDate >= '2025-01-01' AND InvoiceDate < '2026-01-01'
GROUP BY CustomerId
HAVING SUM(Total) > 10
ORDER BY CustomerId;

WHERE first limits input to invoices in 2025. GROUP BY forms customer summaries from that input. HAVING keeps the 18 customers with more than 10 in eligible spending. Their totals mean 2025 spending, not all-time spending.

The half-open date interval includes the start of 2025 and excludes the start of 2026. Comparing the unrounded SUM with the threshold preserves the stated condition; rounding is only for display.

Indexes and selective row predicates can reduce work before grouping. GROUP BY may still require sorting or temporary state. Inspect your engine's plan and measure representative data before claiming that a particular spelling is faster.

COUNT, NULLs and missing groups

SELECT COUNT(*) AS CustomerCount, COUNT(Company) AS KnownCompanies,
       COUNT(*) - COUNT(Company) AS MissingCompanies
FROM Customer;

COUNT(*) counts all 59 customer rows. COUNT(Company) counts only non-NULL company values. An empty string is still a non-NULL value; decide whether your data model treats it as missing.

SUM and AVG ignore NULL inputs. If every value in a group is NULL, SUM and AVG return NULL, while COUNT(*) still counts its rows. A comparison against NULL is unknown, so it does not pass HAVING. SQLite groups NULL keys together; NULL grouping is not the same as the expression NULL = NULL being true.

An aggregate query without GROUP BY still returns one row on empty input: COUNT(*) is zero and SUM/AVG are NULL. A grouped query with no eligible rows returns no groups. It does not invent customers or countries with zero invoices.

SELECT c.CustomerId, COUNT(i.InvoiceId) AS InvoiceCount
FROM Customer AS c
LEFT JOIN Invoice AS i ON i.CustomerId = c.CustomerId AND i.Total > 20
GROUP BY c.CustomerId
ORDER BY c.CustomerId;

This existing lesson query starts with Customer and LEFT JOINs invoices above 20 to retain customers with no matching invoice. COUNT(i.InvoiceId) counts matches; COUNT(*) would also count the preserved customer row and give at least one. The invoice condition stays in ON so unmatched customers survive. See JOIN exercises for that distinction.

Joining invoices to invoice lines can repeat invoice amounts. Summing the repeated invoice Total would overcount revenue. Check the grain after every join, and aggregate at the grain required by your question.

Practice: choose keys, population and threshold

SELECT BillingCountry, COUNT(*) AS InvoiceCount, ROUND(SUM(Total), 2) AS Revenue
FROM Invoice
GROUP BY BillingCountry
HAVING COUNT(*) >= 20
ORDER BY BillingCountry;

SELECT BillingCountry, BillingCity, ROUND(SUM(Total), 2) AS Revenue
FROM Invoice
WHERE InvoiceDate >= '2025-01-01' AND InvoiceDate < '2026-01-01'
GROUP BY BillingCountry, BillingCity
HAVING SUM(Total) > 15
ORDER BY BillingCountry, BillingCity;

References and next steps

• SQLite SELECT: grouping, HAVING and bare columns
• SQLite aggregate functions: COUNT, SUM and AVG
• SQLite EXPLAIN QUERY PLAN

Download every example and solution · SQL Learning Hub and learning path.

Next: compare WHERE and HAVING, then preserve detail with window functions. Related: JOINs vs Subqueries. Practice: aggregation exercises and row-versus-group filtering exercises.

Corrections: report an issue with the query, SQLite version and expected result. AI-assisted writing with automated SQL checks and independent technical review.