SQL GROUP BY: Aggregation and Grouping

An aggregate function combines values from many rows into one result. Oracle also calls it a group function. Without GROUP BY, an aggregate query treats all rows that pass WHERE as one group and returns one summary row.

Start with data you can check

Each row in orders is one order. The numeric columns below contain whole numbers; order_id is the primary key. Dates use ISO text in SQLite and the DATE type in PostgreSQL.

Choose the measure

SUM(amount) adds the amounts. AVG(amount) gives their arithmetic mean. MIN(amount) finds the smallest amount and MAX(amount) finds the largest. You can request all four in one query:

SELECT SUM(amount) AS total, AVG(amount) AS avg_amount, MIN(amount) AS smallest, MAX(amount) AS largest FROM orders;

The result is one row: total 780, average 97.5, smallest 30 and largest 200. Add the eight amounts to get 780, then divide by eight to get 97.5. Each function uses the same eight input rows. Clear aliases after AS make the output headings useful; a generated heading such as sum says little about what was added.

Filter before calculating

To summarize East orders, write SELECT COUNT(*) AS orders, SUM(amount) AS total FROM orders WHERE region = 'East';. The filter keeps amounts 120, 45 and 95. The query returns three orders and a total of 260. WHERE chooses the input rows before any aggregate runs.

An aggregate can also take a calculated expression. In a table with quantity and unit_price, SUM(quantity * unit_price) first finds each row's revenue and then adds it. Two rows, two items at 5 and three items at 10, contribute 10 and 30, for a total of 40. SUM(quantity) * SUM(unit_price) instead multiplies 5 by 15 and gives 75. Keeping the calculation inside SUM preserves each quantity's matching price.

Read extremes correctly

MIN(order_date) is 2026-01-05 and MAX(order_date) is 2026-03-21. Date values and ISO date text sort chronologically. Text such as '03/21/2026' follows character order, which does not reliably match date order. Text extremes also follow the database's collation, its rules for comparing text. Here the customer extremes are 'Ana' and 'Eli'; mixed letter case can change text ordering.

MAX(amount) returns an amount, not the whole order that contains it. To show a customer with a largest order, use SELECT customer, amount FROM orders ORDER BY amount DESC LIMIT 1;. PostgreSQL rejects a plain customer beside MAX(amount) without a suitable grouping. SQLite has a special rule for a single MIN or MAX that can return a neighboring column from the extreme row, but tied extremes still need a tie rule.

“How many?” can ask about rows, recorded values or different values. These are different counts, even when they happen to give the same number in a small table.

Three forms of COUNT

In the orders table, the regions are East, West, East, North, West, East, North and NULL. SELECT COUNT(*) AS order_rows, COUNT(region) AS with_region, COUNT(DISTINCT region) AS regions FROM orders; returns 8, 7 and 3. The repeated region names count separately in COUNT(region), but each name contributes only once to the distinct count.

For “How many different customers ordered?”, use COUNT(DISTINCT customer). Ana, Ben and Chen each appear twice, while Dara and Eli appear once, so the answer is five customers. COUNT(customer) returns eight because every order names a customer. It measures named order rows, not people.

A recorded zero still counts

The discounts are 10, NULL, 5, NULL, 0, NULL, 15 and NULL. COUNT(discount) returns four. The zero records a known value and counts just like 10, 5 and 15. An empty string is also a value in SQLite and PostgreSQL; Oracle treats an empty string as NULL.

Subtract the non-NULL count from the row count to measure missing records: SELECT COUNT(*) - COUNT(region) AS missing_region FROM orders; returns one. This works because both counts use exactly the same input rows.

DISTINCT lists and distinct counts

SELECT DISTINCT region FROM orders; lists East, West, North and one NULL row. Its job is to return different values, and NULL remains in that list. COUNT(DISTINCT region) returns three because the count ignores NULL. Do not infer a distinct count by counting a list unless you have decided whether its NULL row belongs.

DISTINCT can also appear inside SUM or AVG. The quantities are 2, 1, 3, 4, 1, 2, 5 and 1. Their ordinary sum is 19. SUM(DISTINCT quantity) keeps only 1, 2, 3, 4 and 5, giving 15. Repeated quantities usually describe real additional sales. Removing them changes the measure; it does not repair duplicate records.

Count combinations in two steps

For different region-and-customer pairs, make a distinct list first:

SELECT COUNT(*) FROM (SELECT DISTINCT region, customer FROM orders) AS pairs;

The inner query returns five pairs: East/Ana, East/Dara, North/Chen, West/Ben and NULL/Eli. The outer COUNT(*) counts those five rows, including NULL/Eli. This form works in SQLite and PostgreSQL. MySQL also accepts COUNT(DISTINCT region, customer), but that form excludes combinations containing NULL, so it can answer a different question. In SQLite and PostgreSQL the comma-separated form is invalid.

Finally, a count never returns NULL. SELECT COUNT(*) FROM orders WHERE region = 'South'; returns one summary row containing zero, because there are no matching orders.

NULL means that a value is missing or unknown. It changes which values an aggregate sees, so a total or average can be valid SQL while measuring something different from what you intended.

Work out the denominator

Suppose scores contains Ana 80, Ben NULL, Chen 70 and Dara 90. SUM(score) is 240, COUNT(score) is three and COUNT(*) is four. AVG(score) is 80: the three recorded scores add to 240 and are divided by three.

The five standard aggregates ignore NULL arguments. COUNT(*) counts every row because it does not evaluate a column argument. MIN(score) is 70 and MAX(score) is 90; NULL does not become the smallest or largest score. This differs from sorting, where NULL values occupy an end of the result: first in an ascending SQLite sort, last in an ascending PostgreSQL sort.

A manual average needs the non-NULL count: 1.0 * SUM(score) / NULLIF(COUNT(score), 0). The decimal operand keeps fractional results, and NULLIF makes a zero divisor NULL. Dividing by COUNT(*) would give 60 and would effectively treat the missing score as zero.

Choose what a default means

COALESCE returns its first non-NULL argument. If Ben has not been graded, leave the score missing and average the known results. If a missed exam is recorded as NULL but must count as zero, AVG(COALESCE(score, 0)) averages 80, 0, 70 and 90, giving 60. The data's meaning decides whether that replacement is justified.

Compare putting COALESCE inside and outside a function. AVG(COALESCE(score, 0)) changes the values entering the average. COALESCE(AVG(score), 0) changes only a NULL final result; it still gives 80 for these scores. Oracle's NVL(x, 0) is another two-argument form for replacing NULL.

Separate no rows from no recorded values

For WHERE region = 'South', no orders remain. An aggregate query without GROUP BY still returns one row: COUNT(*) is zero, while SUM(amount), AVG(amount), MIN(amount) and MAX(amount) are NULL. To display an empty total as zero, use COALESCE(SUM(amount), 0). SQLite also offers TOTAL(amount), which returns 0.0 for empty input.

Now keep the four orders whose discount is NULL. There are rows, but no recorded discount values: COUNT(*) is four, COUNT(discount) is zero, and SUM(discount) is NULL. Zero recorded values and four missing values are different observations.

Check expressions before they enter SUM

Arithmetic with NULL produces NULL. In SUM(amount - discount), the four orders without a recorded discount contribute no value at all. Only the known-discount rows contribute: 110, 40, 60 and 135, totaling 345.

If a missing discount means no discount, write SUM(amount - COALESCE(discount, 0)). Every order then contributes; total amount 780 minus recorded discounts 30 gives 750. Wrapping the entire sum in COALESCE would not restore the omitted orders because 345 is already non-NULL.

To locate missing values, use IS NULL or IS NOT NULL. WHERE discount IS NULL finds four orders. discount = NULL is never true and therefore cannot select them.

GROUP BY collects rows that share a grouping value. Each aggregate runs separately within each group, and the output contains one row for every group that exists after filtering.

Decide what one result row means

“Total sales per region” asks for one output row per region. Start by writing that sentence, then use region as the grouping column:

SELECT region, COUNT(*) AS orders, SUM(amount) AS total FROM orders GROUP BY region;

The three East amounts, 120, 45 and 95, become one group. Its count is three and its total is 260. North has two orders totaling 350; West has two totaling 140. Eli's order forms a NULL-region group with one order totaling 30. There are four result rows, not eight.

Read the query as a sequence: FROM supplies orders, any WHERE removes rows, GROUP BY collects the remaining rows by region, and the aggregates summarize each collection. SELECT displays its label and measures. Grouping does not discard the smaller orders inside a group; they all contribute unless a row filter has removed them.

Keep missing categories visible

Rows with NULL in the grouping column belong to one NULL group. This is useful: a report can show how much activity has no recorded region. It does not treat every missing region as a different category.

To show a readable label, select COALESCE(region, 'No region') AS region_label and keep GROUP BY region. The label changes the displayed value. To exclude those rows from the calculations, add WHERE region IS NOT NULL before GROUP BY. The resulting totals add to 750, because Eli's amount of 30 has been left out.

A grouped query over an input with no rows returns no groups. For example, filtering the table to South and then grouping by region returns no result rows. That differs from a whole-table aggregate without GROUP BY, which produces one summary row even on empty input.

Sort the groups explicitly

GROUP BY makes no promise about output order. To list the biggest regions first, add ORDER BY total DESC. The alias total is allowed in ORDER BY; ORDER BY SUM(amount) DESC gives the same sort.

A customer report can select the top three totals:

SELECT customer, SUM(amount) AS total FROM orders GROUP BY customer ORDER BY total DESC, customer LIMIT 3;

It returns Chen 350, Ana 165 and Ben 140. The second sort key, customer, gives tied totals a repeatable order. LIMIT 3 returns only three rows; it does not keep every tie at the boundary. SQL Server expresses this limit with SELECT TOP 3, while Oracle supports FETCH FIRST 3 ROWS ONLY.

When sorting category labels ascending, SQLite normally places NULL first and PostgreSQL places it last. Specify the intended ordering when the position matters.

Check the grouping column

SELECT DISTINCT region FROM orders and SELECT region FROM orders GROUP BY region return the same distinct region labels, including NULL. Use grouping when you also need a measure for each label.

Grouping by the unique order_id gives eight groups of one row each. Every count is one, and each amount total equals its original amount. For a regional report, that result signals that the grouping column is too detailed.

A grouped report has to fit many input rows into one result row. Every displayed value therefore needs a clear meaning for the whole group. This is why SQL restricts the columns you can select.

Follow the portable grouping rule

An item in a grouped SELECT should be a grouping column, an expression built from grouping columns, an aggregate or a constant. Consider:

SELECT region, customer, COUNT(*) FROM orders GROUP BY region;

East contains Ana, Ana and Dara. The group has one region and one count, but two different customer names. The query has not said which customer a single East result should display. PostgreSQL rejects the ungrouped customer column.

The same issue arises without GROUP BY: SELECT customer, SUM(amount) FROM orders combines a table-wide total with an individual name. The total has no single customer label.

Repair the query to match the question

There are three useful repairs, each with a different meaning:

• For one row per region, remove customer: SELECT region, COUNT(*) FROM orders GROUP BY region;.
• For one row per region and customer, group by both: SELECT region, customer, COUNT(*) FROM orders GROUP BY region, customer;.
• For the number of customers per region, aggregate the names: SELECT region, COUNT(DISTINCT customer) AS customers FROM orders GROUP BY region;.

The last query reports two customers in East and one in each other region group. Simply adding a column to GROUP BY can change the report's level of detail, so choose the repair from the question, not just from the error message.

A grouping column may be omitted from SELECT. SELECT COUNT(*) FROM orders GROUP BY region; is valid and returns four counts: three, two, two and one, in no promised order. Those numbers are difficult to use without their region labels.

Apply the rule beyond SELECT

A grouped ORDER BY customer has the same problem when grouping only by region: which customer should determine East's position? Grouped HAVING conditions must also describe a whole group. Use grouped columns or aggregate expressions there.

Recognize permissive database behavior

SQLite accepts a bare column, a selected column that is neither grouped nor aggregated. It takes a value from one input row of the group. An apparently successful result such as 'Ana' beside East does not mean Ana represents every order in that region.

With exactly one built-in MIN or MAX aggregate, SQLite takes bare values from a row holding the extreme. Ties and multiple extreme aggregates make the selected row uncertain. This shortcut is database-specific. Sorting the original rows is clearer when you want an order with an extreme amount.

MySQL's default ONLY_FULL_GROUP_BY mode rejects ungrouped values that are not determined by the group. ANY_VALUE(customer) explicitly permits an arbitrary value, but does not find the customer associated with a chosen total. SQL Server and Oracle also reject ordinary ungrouped columns.

PostgreSQL and MySQL recognize a primary-key exception: grouping by a table's primary key identifies a single row, so its other columns are determined. SELECT order_id, customer, SUM(amount) FROM orders GROUP BY order_id works in PostgreSQL. Listing every displayed plain column in GROUP BY remains the portable approach.

Some reports need a combination of categories, or a category calculated from each row. SQL can group by several columns or by an expression. The important choice is still what one output row stands for.

Group by a combination

GROUP BY region, customer puts rows together only when both values match. On the orders table, run:

SELECT region, customer, COUNT(*) AS orders, SUM(amount) AS total FROM orders GROUP BY region, customer ORDER BY region, customer;

The results are East/Ana: two orders, 165; East/Dara: one, 95; North/Chen: two, 350; West/Ben: two, 140; and NULL/Eli: one, 30. The two East results add to the regional total of 260.

Grouping by customer, region forms the same combinations. The column order in GROUP BY does not sort the output. The separate ORDER BY above chooses how to display it, subject to each database's default position for NULL.

Only combinations present in the input appear. There is no West/Ana row with a count of zero. Grouping summarizes existing rows; it does not generate every possible pairing of category labels.

Add detail only when needed

Adding a column splits a group only if its rows contain different values in that column. Grouping by customer alone already gives five groups here. Adding region still gives five, because each customer has just one region value. Adding order_date to a regional report creates smaller groups for the separate dates and changes the question being answered.

Group dates by year and month

In SQLite, use strftime('%Y-%m', order_date) to create a label such as '2026-02':

SELECT strftime('%Y-%m', order_date) AS month, COUNT(*) AS orders, SUM(amount) AS total FROM orders GROUP BY strftime('%Y-%m', order_date) ORDER BY month;

January has two orders totaling 200, February has three totaling 305, and March has three totaling 275. In PostgreSQL, replace both occurrences of strftime('%Y-%m', order_date) with to_char(order_date, 'YYYY-MM') for a DATE column.

Grouping by month number alone combines the same month across years. January 2025 and January 2026 would become one group. Keeping the year in the grouping expression preserves separate calendar months.

Repeating the expression in GROUP BY makes the grouping explicit. SQLite, PostgreSQL and MySQL also accept the output alias, such as GROUP BY month; SQL Server does not accept a same-level SELECT alias there.

Build categories with CASE

A CASE expression can label each order before grouping:

SELECT CASE WHEN amount >= 100 THEN 'large' ELSE 'small' END AS size, COUNT(*) AS orders, SUM(amount) AS total FROM orders GROUP BY CASE WHEN amount >= 100 THEN 'large' ELSE 'small' END;

The large category contains 120, 200 and 150: three orders totaling 470. The small category contains five orders totaling 310. The threshold is inclusive, so an amount of exactly 100 belongs to 'large'. The same CASE expression appears in the display and the grouping so both use the same category definition.

HAVING tests groups after their aggregates have been computed. Use it for a request such as “regions with total sales above 200.” The condition describes each regional total, not any single order.

Calculate first, keep groups second

SELECT region, COUNT(*) AS orders, SUM(amount) AS total FROM orders GROUP BY region HAVING SUM(amount) > 200;

Before HAVING, the regional totals are East 260, North 350, West 140 and NULL 30. The condition keeps East with three orders and North with two. It removes the other two result groups; it does not remove selected orders from inside East or North.

In written SQL, place HAVING after GROUP BY and before ORDER BY. Sorting then applies to the groups that survived the condition.

Filter on measures you do not display

SELECT region FROM orders GROUP BY region HAVING COUNT(*) >= 2; returns East, North and West. The database can count a group's rows to decide whether it qualifies without displaying that count. HAVING and SELECT do not need identical aggregate lists.

You can combine group conditions. HAVING COUNT(*) >= 2 AND AVG(amount) > 80 keeps East and North. East has three orders and an average of about 86.67; North has two and an average of 175. West has enough orders but its average is only 70. Both tests must pass because they are joined with AND; OR would allow either test to pass.

Find repeated values

Grouping and counting also finds duplicates:

SELECT customer, COUNT(*) AS orders FROM orders GROUP BY customer HAVING COUNT(*) > 1;

Ana, Ben and Chen each have two orders and qualify. Dara and Eli have one and do not. To find repeated email values in a customer table, the same pattern groups by email and keeps counts above one. The count is the number of records sharing that value.

Use the expression the condition needs

Repeat the aggregate in HAVING: HAVING SUM(amount) > 200. PostgreSQL and standard SQL do not allow the output alias total in that same-level condition, although SQLite and MySQL do. An alias is safe in a later ORDER BY total DESC.

A displayed rounded value and the value tested can differ. East's exact average is 260 divided by three. If you select ROUND(AVG(amount), 2) but write HAVING AVG(amount) > 86.67, East is removed: its unrounded average is below 86.67. North remains. The condition uses exactly the expression written in HAVING. Only HAVING ROUND(AVG(amount), 2) ... would test the rounded value.

Filter the single overall group

HAVING can also operate without an explicit GROUP BY. SELECT COUNT(*) FROM orders HAVING COUNT(*) > 5; treats all eight orders as one group, passes the test and returns a row containing eight. Replacing the condition with COUNT(*) > 10 returns no rows, not a row containing zero.

PostgreSQL supports this pattern, as does SQLite from version 3.39.0. In an older SQLite installation, a query using HAVING needs an explicit GROUP BY.

A condition belongs where the thing it describes exists. Individual orders exist before grouping. A regional total exists only after the orders have been grouped and added.

Trace the logical order

SQL is written as SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. To reason about the result, follow this logical order:

1. FROM supplies the rows.
2. WHERE keeps qualifying rows.
3. GROUP BY forms groups, and aggregates calculate their measures.
4. HAVING keeps qualifying groups.
5. SELECT produces the output values; DISTINCT removes duplicate output rows if requested.
6. ORDER BY sorts the result, and LIMIT keeps the requested number of rows.

The database may arrange its internal execution differently.

Put each condition at its level

WHERE SUM(amount) > 200 fails because the total does not yet exist at the row-filtering stage. A row condition such as region = 'East' can be decided before grouping and belongs in WHERE.

Work through both filters

SELECT region, COUNT(*) AS orders, SUM(amount) AS total FROM orders WHERE amount >= 60 GROUP BY region HAVING SUM(amount) > 200;

First remove the orders of 45 and 30. East now contains 120 and 95, North contains 200 and 150, and West contains 80 and 60. Next calculate totals of 215, 350 and 140. Finally keep totals above 200. The output is East with two orders and 215, and North with two and 350. The NULL region has no remaining rows, so it never forms a group.

Do not change the question while moving a filter

“For each region, average only orders of 60 or more” uses WHERE amount >= 60. East's average becomes 107.5 from 120 and 95.

HAVING MIN(amount) >= 60 asks for regions where every non-NULL amount meets the threshold. East is removed because its minimum is 45; North and West remain with their original orders. Moving the test changed the meaning from selecting individual orders to accepting or rejecting whole regions.

For this regional report, region <> 'West' gives the same result in WHERE or HAVING: East and North remain, and the NULL group also fails the comparison. WHERE states the row restriction directly and applies it before grouping. To keep missing regions as well, the condition needs an explicit OR region IS NULL.

SELECT aliases are unavailable in earlier WHERE and HAVING clauses in PostgreSQL. SQLite permits more alias use, but spelling out the input column or expression keeps the intended stage clear.

Dividing two whole-number values can discard the fraction before you ever display the answer. That matters when you build a mean or percentage from totals and counts.

Keep a decimal operand before division

In SQLite and PostgreSQL, 7 / 2 returns 3. With integer input, SUM(quantity) / COUNT(*) on the orders table returns 2: 19 items divided by eight orders loses its fractional part. PostgreSQL's SUM of an INTEGER column returns BIGINT, which still uses integer division here.

Write 1.0 * SUM(quantity) / COUNT(*) to obtain 2.375 in both engines. Multiplying by 1.0 creates a decimal operand before the division. SUM(quantity) / COUNT(*) * 1.0 gives 2.0 because the division has already discarded the fraction. Operations of the same precedence are evaluated from left to right.

For this table, AVG(quantity) directly gives 2.375 in SQLite and PostgreSQL. The manual formula matters when you need a different denominator or a percentage.

Calculate a percentage

Three of the eight orders have amounts of at least 100. A count of those orders divided by the count of all orders measures their share. Scale by 100.0:

SELECT 100.0 * COUNT(CASE WHEN amount >= 100 THEN 1 END) / COUNT(*) AS pct_large FROM orders;

The result is 37.5. Using the integer 100 instead produces 37 in SQLite and for integer count division in PostgreSQL. Rounding that later would only change how 37 is displayed.

A whole-table query may have no matching rows. NULLIF(COUNT(*), 0) returns NULL when the count is zero, so 100.0 * COUNT(discount) / NULLIF(COUNT(*), 0) returns NULL for an empty input. An unguarded zero denominator raises an error in PostgreSQL; SQLite returns NULL. A NULL share can clearly represent “no orders to measure.”

Check the database and input type

In SQL Server, use AVG(quantity * 1.0) or cast inputs to DECIMAL before averaging. In PostgreSQL, SUM of a BIGINT column returns NUMERIC, so its division need not behave like SUM of an INTEGER column. MySQL returns DECIMAL for sums of integer inputs.

Casting can also create the needed decimal operand: use CAST(SUM(quantity) AS REAL) in SQLite or CAST(SUM(quantity) AS NUMERIC) in PostgreSQL. SQLite's NUMERIC cast can retain an integer, so that cast alone does not ensure decimal division.

Separate exact decimals from approximations

Floating-point types store binary approximations. PostgreSQL NUMERIC and DECIMAL support exact decimal values; floating-point additions can display small artifacts such as 0.30000000000000004. SQLite's NUMERIC affinity does not provide exact decimal storage. For exact money totals there, store integer cents, sum them, and divide by 100.0 for display.

Rounding chooses how many digits a result shows. Keep the full calculation until the final step so that display choices do not change the measure you are calculating.

Round an aggregate result

ROUND(x, n) rounds to n digits after the decimal point. ROUND(x) rounds to a whole number. For example, ROUND(3.14159, 2) is 3.14.

SELECT region, ROUND(AVG(amount), 2) AS avg_amount FROM orders GROUP BY region; gives East 86.67, North 175, West 70 and NULL-region 30. SQLite may show 175.0 while PostgreSQL's NUMERIC result shows 175.00. These represent the same number. Rounding a number and formatting a fixed-width text label are different jobs.

Round after adding or averaging

For three fees of 0.44, add first to get 1.32, then round to one decimal to get 1.3. ROUND(SUM(fee), 1) follows that sequence. SUM(ROUND(fee, 1)) rounds each fee to 0.4 and then adds them, giving 1.2. Early rounding changed the total.

Likewise, ROUND(100 * 1 / 3, 1) gives 33.0 in SQLite and PostgreSQL because integer division has already produced 33. ROUND(100.0 * 1 / 3, 1) preserves the fraction and gives 33.3. Choose the calculation type before rounding.

Rounded shares may not add to 100%. Three equal shares can each display as 33.3%, totaling 99.9%. The underlying shares still cover the whole. Recalculating one arbitrary share to force the display total changes what that displayed number means.

Use a supported input type

PostgreSQL supports two-argument ROUND(value, n) for NUMERIC. If temp is DOUBLE PRECISION, write ROUND(CAST(AVG(temp) AS NUMERIC), 2). Averaging 20.5, 21.25 and 19.0 gives 20.25, and the cast lets you use two-argument rounding. With integer amount, PostgreSQL's average is already NUMERIC, so no cast is needed.

For exact PostgreSQL NUMERIC values, halfway cases round away from zero: 2.5 becomes 3 and -2.5 becomes -3. SQLite also gives 3.0 and -3.0 for these examples. PostgreSQL floating-point rounding is platform-dependent; nearest-even behavior is common. Binary approximation can also place a decimal just below an intended halfway point. Do not use a particular floating-point tie as a general rule for exact decimal arithmetic.

Notice two other dialect differences

PostgreSQL, MySQL, SQL Server and Oracle use a negative number of decimal places to round to the left of the decimal point. ROUND(1234.5, -2) gives 1200. SQLite treats a negative second argument as zero and gives 1235.0. To round to hundreds in SQLite, use ROUND(x / 100.0) * 100.

Casting to an integer is also database-specific: SQLite cuts off the fractional part, so 2.9 becomes 2; PostgreSQL rounds it to 3. Use ROUND when rounding is the purpose. PostgreSQL's TRUNC(x, n) cuts off digits instead: TRUNC(2.97, 1) is 2.9, while rounding it to one decimal gives 3.0.

Conditional aggregation lets different measures use different contributions while keeping the same overall set of rows. A CASE expression decides what each row contributes to a count or sum.

Count matching rows

COUNT(CASE WHEN amount >= 100 THEN 1 END) counts orders of at least 100. The expression returns 1 for a match and NULL for other rows because it has no ELSE. COUNT skips those NULLs, leaving three matches in the orders table.

SUM(CASE WHEN amount >= 100 THEN 1 ELSE 0 END) counts matches by adding ones and zeros. With existing input rows it gives the same count. COUNT(CASE WHEN amount >= 100 THEN 1 ELSE 0 END) does not: both 1 and 0 are non-NULL, so it counts all eight orders.

Add matching amounts

For a conditional total, return the amount itself:

SELECT region, COUNT(*) AS orders, COUNT(CASE WHEN amount >= 100 THEN 1 END) AS large_orders, SUM(CASE WHEN amount >= 100 THEN amount ELSE 0 END) AS large_total FROM orders GROUP BY region;

East has three orders, one large order and a large total of 120. North has two orders, both large, totaling 350. West has two orders, no large orders and a large total of zero. The NULL-region group has one order and zeros for both large measures.

The output keeps West because its input rows still exist. Adding WHERE amount >= 100 would remove West's rows before grouping, so West would disappear. That filter would also make COUNT(*) count only large orders, losing the all-order comparison.

Use the right denominator

Within each region, calculate the percentage of large orders with ROUND(100.0 * COUNT(CASE WHEN amount >= 100 THEN 1 END) / COUNT(*), 1) AS pct_large. East gives 33.3, North 100.0, West 0.0 and the NULL group 0.0. Each denominator is that group's order count, not the table-wide count of eight.

For an ungrouped report that can have no rows, guard the denominator with NULLIF(COUNT(*), 0). A count on an empty input is zero, but a percentage of an empty population has no denominator.

Make several category columns

A single result can show one count per region:

SELECT COUNT(CASE WHEN region = 'East' THEN 1 END) AS east, COUNT(CASE WHEN region = 'West' THEN 1 END) AS west, COUNT(CASE WHEN region = 'North' THEN 1 END) AS north FROM orders;

The counts are three, two and two. Eli's order belongs to none of these named-region tests. Add COUNT(CASE WHEN region IS NULL THEN 1 END) to count the one missing-region order. Comparing a NULL region to a name does not produce a true condition.

Recognize alternative syntax

PostgreSQL and SQLite from version 3.30.0 support COUNT(*) FILTER (WHERE amount >= 100). The filter applies to that aggregate, so an adjacent unfiltered COUNT(*) still counts all orders. MySQL and SQL Server do not support this aggregate FILTER syntax; the CASE form works on both.

SQLite and MySQL also allow SUM(amount >= 100) because comparisons produce numeric truth values. PostgreSQL rejects SUM of booleans. Use the CASE form when a query should work across these engines.

A summary query starts with a question about a population at a particular level of detail. Translate those choices before writing SQL, then check that the output still answers the original question.

Build the report in order

Ask: “For each month of 2026 with at least three orders, how many orders were placed and what was the average amount, to two decimals?”

1. The measures are COUNT(*) and AVG(amount).
2. The grain, or meaning of one result row, is one calendar month.
3. The row condition keeps dates in 2026.
4. The group condition requires at least three orders.
5. The output should be sorted by month, with the average rounded for display.

In SQLite:

SELECT strftime('%Y-%m', order_date) AS month, COUNT(*) AS orders, ROUND(AVG(amount), 2) AS avg_amount FROM orders WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01' GROUP BY strftime('%Y-%m', order_date) HAVING COUNT(*) >= 3 ORDER BY month;

For PostgreSQL date values, replace the month expression in both places with to_char(order_date, 'YYYY-MM'). The date range includes the start of 2026 and excludes the start of 2027.

January has two orders and fails the group condition. February has three orders totaling 305, with average 101.67. March has three totaling 275, with average 91.67. The surviving groups account for six orders, not eight, because HAVING removed January.

Check before filtering groups away

Run the report without HAVING first. Counts across all groups should add to the row count under the same WHERE filter, and totals should add to the ungrouped total. Here those checks give eight and 780. Do not compare retained-group totals with the whole input after some groups have been excluded.

Look for a NULL group, inspect the order of results, and recompute a group by filtering the raw table. For East, WHERE region = 'East' should give three orders totaling 260. A discrepancy can reveal an extra grouping column, a misplaced row filter or an expression that became NULL.

Distinguish an average order from an average customer

AVG(amount) is 97.5 per order. Average spending per customer asks for a different unit: the five customer totals are 165, 140, 350, 95 and 30. Their average is 156.

Calculate that in two stages:

SELECT AVG(total) AS avg_customer_total FROM (SELECT customer, SUM(amount) AS total FROM orders GROUP BY customer) AS per_customer;

The inner query creates one row per customer. The outer query averages those rows, giving each customer one contribution. With all customers named, 1.0 * SUM(amount) / COUNT(DISTINCT customer) gives the same 156. Unequal order counts make averaging order amounts a different calculation.

Summarize the groups themselves

To count customers with more than one order, first list them and then count their rows:

SELECT COUNT(*) FROM (SELECT customer FROM orders GROUP BY customer HAVING COUNT(*) > 1) AS repeat_customers;

The answer is three. Directly nesting aggregates, as in MAX(AVG(amount)) at one query level, fails in SQLite and PostgreSQL. To find the region with the highest average, sort the grouped averages: ORDER BY AVG(amount) DESC LIMIT 1 returns North with 175. Oracle also supports nested group functions, but a separate query level makes the two calculations explicit. Check ties when a report asks for a top result.