Can an aggregate query return many rows?

Yes. Grouping can return one summary row for each group, subject to further filtering.

How do WHERE and HAVING differ?

WHERE selects input rows; HAVING selects groups after grouping and aggregation in the logical query model.

Is a window function the same thing?

Not exactly. Window calculations can attach summaries to individual rows instead of collapsing them into ordinary grouped output.

AI Glossary / Data engineering and architecture / Beginner

Aggregate Query

Definition

An aggregate query summarizes multiple database rows using functions such as COUNT, SUM, AVG, MIN or MAX. It may calculate one summary for the selected data or separate summaries for groups, such as total sales by region. Its result describes the chosen collection rather than simply reproducing every source row.

What it means

A detail query might show each course enrollment. An aggregate query might show how many enrollments each course received. Filters, groups and calculations produce different summaries.

The most important question is what each input row represents. A table of orders, a table of order lines and a joined table of orders and payments have different possible counting units. A query can execute successfully and still report a misleading business number if it summarizes the wrong unit.

Aggregation also loses detail. A group average does not show every person's experience, and a total does not explain how values are distributed. Good reporting often pairs summary results with a way to inspect their contributing records.

How it works

In a typical SQL query, FROM and joins establish the input, WHERE filters rows, GROUP BY identifies groups and aggregate functions compute summaries. HAVING can filter groups according to their aggregated results. This is a logical explanation of the query, not a promise about the database's physical execution order.

Without grouping columns, an aggregate can summarize the selected input as a whole. With GROUP BY course_id, it calculates results separately for each course identifier. Ordinary selected columns must fit the query's grouping rules rather than ambiguously taking a value from an unspecified member row.

Null handling is function-specific. COUNT(*) counts rows; COUNT(score) counts non-null score values. AVG(score) ignores null score inputs in ordinary SQL aggregate behavior. These choices change the denominator.

For PostgreSQL, SUM over no selected rows returns NULL, while COUNT returns zero. A report may deliberately substitute zero, but should distinguish no observations from an observed total of zero when that difference matters.

Three practical examples

A training provider counts completed enrollments by course after filtering out cancellations and incomplete registrations.
An operations dashboard reports the maximum recorded waiting time and the average waiting time for each service queue.
An AI evaluation team groups labeled test examples by category and calculates separate error counts, making the denominator for each rate explicit.

Think of it this way

Think of a cashier counting a jar of receipts into daily totals. The daily number is a summary of the receipts placed in that day's pile. Putting one receipt into the pile twice changes the total even when the arithmetic is perfect.

The analogy stops short of database semantics. Queries must also handle null values, joins, types and possibly very large inputs. The lesson is that a reliable calculation starts with a correctly defined collection, not merely the choice of an adding or averaging function.

Worked example

Three counts from four records

Consider an invented score table containing these course and score pairs: A with 80, A with 100, A with NULL, and B with 60. NULL means that a score has not been recorded, not that the learner scored zero.

For course A, COUNT(*) is 3, COUNT(score) is 2, SUM(score) is 180 and AVG(score) is 90. Dividing 180 by all three rows would instead give 60, which answers a different question by treating the missing result as though it contributed zero.

For course B, both counts are 1, the sum is 60 and the average is 60. A HAVING condition requiring COUNT(score) >= 2 retains A and excludes B after the groups are formed.

The overall average of the recorded scores is 240 divided by 3, or 80. Averaging the two course averages, 90 and 60, gives 75 because it weights courses equally rather than individual recorded scores. Neither weighting should be chosen accidentally.

Illustrative case study

A fictional retailer joins orders to order lines and sums a total stored on each order. An order worth 120 has three lines, so the joined input repeats its total three times. The report counts 360 for that order.

The analyst checks the intended reporting unit and changes the query to summarize each order once, or to sum the appropriate line amounts when those are the intended measure. The team adds a small test containing orders with different numbers of lines.

It avoids treating SUM(DISTINCT order_total) as a general repair: two different orders can legitimately have the same total. The case demonstrates how join multiplicity can distort an aggregate even though no source record is itself duplicated incorrectly.

Common misconceptions and mistakes

COUNT(column) always equals COUNT(*). Null values can make the first smaller.
The average of group averages is always the overall average. Group sizes and the intended weighting determine whether this is valid.
DISTINCT is a universal fix for inflated totals. It may remove legitimate repeated values while failing to express the intended business unit.

Business use

Aggregate queries power management reports, product analytics and model monitoring. Their definitions should name the population, time window, grouping level, treatment of missing data and measurement unit. These details clarify whether similarly named metrics mean the same thing.

For AI-generated SQL, review joins and denominators before trusting a polished chart. A plausible query can count joined rows instead of customers or average rates with incompatible weights. Use small examples where the expected answer can be calculated manually.

For larger workloads, assess execution plans and appropriate summaries or indexes after the meaning is correct. Preserve enough lineage to explain which records and rules contributed to a published metric.

Check your understanding

A group has scores 10, 20 and NULL. What are COUNT(*), COUNT(score) and AVG(score)?

Answer: They are 3, 2 and 15 respectively under the ordinary SQL behavior described here. The missing score is not treated as zero.

Related terms

Weighted average
Aggregate function
GROUP BY
HAVING
WHERE
null
join
data grain
window function

Learn next

Read grouping and join cardinality. Build a five-row example with a missing value and one repeated entity, calculate the expected counts manually and compare them with several aggregate queries.

Sources

PostgreSQL, Aggregate Functions tutorial: https://www.postgresql.org/docs/15/tutorial-agg.html. Primary explanation of grouping and filtering.

PostgreSQL, Value Expressions: https://www.postgresql.org/docs/current/sql-expressions.html. Primary documentation of aggregate inputs, DISTINCT and counting behaviour.

PostgreSQL, Aggregate Functions reference: https://www.postgresql.org/docs/15/functions-aggregate.html. Primary documentation of null and empty-input behaviour. The score and retailer cases are original illustrations.

Previous: Aggregate (data warehouse) Next: Aggregation