SQL WHERE and GROUP BY: turn rows into a useful report

When you start writing reports in SQL, two questions appear quickly: which records should I include, and how should I summarize them? WHERE helps with the first and GROUP BY with the second. I start one step earlier: what does one source row represent, and what should one output row represent? Without those definitions, valid SQL can answer the wrong question very efficiently.

I will build a city summary of paid orders, then introduce date boundaries, a missing amount and a threshold for whole groups. All data is synthetic, and the examples use PostgreSQL 18 syntax. The expected results come from the documented rules and a manual walkthrough of six records.

If SELECT ... FROM ... is still unfamiliar, start with the SELECT introduction. Here, I focus on the route from input records to a report. Each stage should answer a specific question, and the small dataset makes mistakes visible without requiring a large database or a downloadable source file.

Define one source row first

One row of orders represents one order. order_id identifies it uniquely. city supplies its city, status its state, amount a numeric measure and ordered_on a date without a time. I do not assign a currency to the amounts. The purpose is to understand filtering and aggregation without introducing extra accounting conventions.

The setup below creates a temporary table in an empty practice connection. Later queries assume that table is available. If you try the exercise yourself, use a suitable practice environment. You can also read the table and work through the results on paper; that is enough to follow the transformations.

CREATE TEMP TABLE orders (
    order_id integer PRIMARY KEY,
    city text,
    status text,
    amount numeric,
    ordered_on date
);
INSERT INTO orders VALUES
    (1, 'Krakow',  'paid',      100, DATE '2026-10-01'),
    (2, 'Krakow',  'paid',       50, DATE '2026-10-02'),
    (3, 'Warsaw',  'paid',       80, DATE '2026-10-02'),
    (4, 'Krakow',  'cancelled',  20, DATE '2026-10-03'),
    (5, 'Warsaw',  'paid',     NULL, DATE '2026-10-04'),
    (6, 'Warsaw',  'paid',       40, DATE '2026-11-01');

The cancelled order, November date and missing amount are deliberate. If every order were paid, on the same day and fully populated, several incorrect queries could produce the expected answer by coincidence. A useful small example contains records that separate the intended rule from plausible mistakes.

The report will have a different level of detail: one row per city. I no longer expect an individual order identifier in each output row. Several source identifiers can contribute to one summary. Decide which details remain meaningful at that level before choosing columns for the final SELECT list.

WHERE selects records for analysis

First, I want the paid orders from Krakow. I select identifiers and amounts so the result is easy to inspect. The city literal matches the synthetic source value Krakow. Real datasets may use other spellings or identifiers, which need their own consistency rules.

SELECT order_id, amount
FROM orders
WHERE city = 'Krakow'
  AND status = 'paid'
ORDER BY order_id;

The expected rows are order 1 with amount 100 and order 2 with amount 50. Order 4 has the right city but is cancelled, so it is excluded. Explain that in plain language: both conditions have to hold for an order to enter the result.

PostgreSQL’s table-expression documentation describes WHERE’s role in logical processing. Imagining a condition applied to each row is useful for understanding its meaning. It does not prove that the engine physically scans the entire table in that order. The access method depends on the execution plan.

Combine AND and OR deliberately

Suppose the requirement is paid orders from either Krakow or Warsaw. I put the city alternatives inside parentheses. The paid-status requirement then clearly applies to both alternatives. This also makes the query readable without asking the reviewer to reconstruct operator precedence from memory.

SELECT order_id, city
FROM orders
WHERE status = 'paid'
  AND (city = 'Krakow' OR city = 'Warsaw')
ORDER BY order_id;

Orders 1, 2, 3, 5 and 6 pass this filter. Compare it with status = 'paid' AND city = 'Warsaw' OR city = 'Krakow': the final alternative would also admit the cancelled Krakow order. One record is enough to reveal a substantial difference between the intended rule and its implementation.

For longer filters, I put conditions on separate lines and read them as a requirement. If the sentence says “and either of these two,” I expect a corresponding group in the code. A few extra characters are a small price for avoiding an unexplained extra order in a report.

Set explicit date boundaries

Next, I restrict the result to paid orders in October. I use an inclusive start and an exclusive start of the next month. The column here is a date, and the literals explicitly use the selected engine’s date syntax.

SELECT order_id, city, amount
FROM orders
WHERE status = 'paid'
  AND ordered_on >= DATE '2026-10-01'
  AND ordered_on < DATE '2026-11-01'
ORDER BY order_id;

Orders 1, 2, 3 and 5 remain. The November order is excluded exactly at the upper boundary. This style also gives a useful starting point for timestamp data, but timestamps require additional decisions about types and time zones. The current example only settles dates without times.

BETWEEN includes both endpoints, as the comparison documentation explains. It is not inherently wrong; its boundaries have to match the question. I prefer adjacent reporting periods whose shared boundary belongs to the later period. That makes the inclusion decision explicit instead of hiding it inside an informal phrase such as “until November.”

GROUP BY changes the output’s detail

I will summarize the paid October orders by city. GROUP BY city creates groups, and aggregate functions describe the records within each group. The SELECT list contains the city and the resulting measures. Someone reading the output should be able to understand those measures without opening the source query.

SELECT
    city,
    COUNT(*) AS orders_count,
    COUNT(amount) AS known_amounts,
    SUM(amount) AS total_amount,
    AVG(amount) AS average_known_amount
FROM orders
WHERE status = 'paid'
  AND ordered_on >= DATE '2026-10-01'
  AND ordered_on < DATE '2026-11-01'
GROUP BY city
ORDER BY city;

For Krakow, 100 and 50 total 150, giving an average of 75. Warsaw has two orders but only one known amount, 80. Its average is therefore 80 rather than 40. Unless the report makes that denominator clear, a reader can interpret the same number in a reasonable but incorrect way.

NULL, zero and the meaning of a count

The aggregate-function documentation distinguishes COUNT(*), which counts rows, from COUNT(amount), which counts non-null amounts. AVG uses the known numeric values. A missing amount does not automatically become zero. An actual zero is a value and participates in the average.

I show both counts beside the total. That way, Warsaw’s missing amount remains visible instead of looking like a complete set of two observations. Whether that missing value is acceptable is a business decision. SQL cannot infer whether an order is awaiting an amount or whether an export has lost it.

To find missing amounts, use amount IS NULL. The expression amount = NULL is not a missing-value test. Likewise, a row with NULL will not pass amount > 0, because WHERE keeps a true condition. If the report should disclose missing values separately, make that requirement explicit.

Also distinguish a missing group from a group containing missing measures. A city report does not invent a row for a city absent after filtering. Ask whether the reader needs observed cities only or a complete reference list with an explicit no-data status. That question changes the required dataset, not merely the formatting of the result.

HAVING tests a whole group

Now suppose the report should show only cities whose paid October orders total more than 100. That condition applies to a group. I put it in HAVING while keeping the date and status filters in WHERE.

SELECT city, SUM(amount) AS total_amount
FROM orders
WHERE status = 'paid'
  AND ordered_on >= DATE '2026-10-01'
  AND ordered_on < DATE '2026-11-01'
GROUP BY city
HAVING SUM(amount) > 100
ORDER BY total_amount DESC, city;

Krakow remains with a total of 150. Warsaw’s known total is 80, below the threshold. Moving the condition to WHERE amount > 100 would ask a different question: include only individually large orders before calculating totals. None of the October orders exceeds 100, so that version would produce no city rows.

This comparison is more useful than memorizing definitions in isolation. Before choosing the clause, ask whether the threshold concerns one record or a collection of records. If it concerns the city’s total, the query needs a condition on the aggregate. The table-expression documentation covers the relevant GROUP BY and HAVING rules.

Group by more than one column

Adding status to GROUP BY changes one result row into a city-status combination. With no date or paid-status filter, Krakow has two groups: paid with two orders and cancelled with one. Warsaw has one paid group containing three orders.

SELECT city, status, COUNT(*) AS orders_count
FROM orders
GROUP BY city, status
ORDER BY city, status;

There is no automatic Warsaw-cancelled group with a zero count. That combination does not occur in the input. A business reader may nevertheless expect every possible combination. Completing the grid is a separate task that needs a reference list of cities and statuses, rather than a different label on the same aggregation.

PostgreSQL allows positional grouping using SELECT-list numbers, described in its SELECT documentation. For an introductory report, I use city, status instead. Explicit names are easier to review when selected columns move. Do not assume that GROUP BY 1, 2 is supported identically by every SQL engine.

Order the result and name its measures

GROUP BY does not promise sorted output. If the report should start with the largest total, add ORDER BY. The sorting documentation also describes subsequent keys for resolving ties. In this small city report, I add city after the total so equal totals have a defined order.

Output names should preserve the meaning of the calculation. average_known_amount is longer than avg, but reminds me that missing amounts did not enter the denominator. The presentation can use a friendlier label. What matters is keeping that definition when the result moves into a dashboard, spreadsheet or shared extract.

I avoid adding a source column to SELECT unless it has a clear meaning for each group. An order identifier beside a city total raises the question: which order? If the report needs individual records and group context together, consider a separate detail view or learn window functions next. A convenient-looking column should not make the result ambiguous.

Check the report before sharing it

Before handing over a grouped result, I write down the source-row definition, inclusion conditions and output-row definition. Then I pick one city and reconstruct its count and total from a detail list. This checks the meaning of the transformation instead of merely confirming that the database accepted the query.

For an exercise, change HAVING to COUNT(*) >= 2. Both cities should remain for paid October orders. Compare that with COUNT(amount) >= 2, which retains only Krakow. Explain in your own words why changing the count’s argument changes the decision about the whole group.

Then expand the reporting period to include November. Warsaw gains one known amount of 40 and one order. Before changing the query, predict which measures should increase and which denominator the average should use. A small expected change makes a useful reference when reviewing a revised report.

With real data, also check duplicate identifiers, the city-assignment rule and source completeness. If another table is joined before grouping, make sure the join does not multiply orders. GROUP BY can hide extra input rows inside an elegant-looking total. A tidy output table still needs a trustworthy route from source records to measures.

Reconcile the summary with the detail

The paid October detail contains four records. The two city counts are two and two, so they should reconstruct that record count. The known amounts are 100, 50 and 80, totalling 230. The two reported city totals, 150 and 80, should also total 230. Those are small, concrete acceptance checks for this exercise.

Averages need more care. You cannot obtain the overall average known order amount by simply averaging the city averages. Krakow contributes two known amounts and Warsaw only one. Giving the two city averages equal weight would answer a question about equally weighted cities, rather than equally weighted known orders. Decide which unit the question concerns before combining summary measures.

Distinct counts need similar attention when you extend a report. A customer who appears in two cities could be counted once in each city. Adding those distinct city counts would then count that customer twice. Our table has no customer identifier, so I do not pretend it supports a customer measure. First obtain the necessary field and define what entity it identifies.

An empty output also needs interpretation. A city-grouped query over a period without orders produces no city rows. That screen alone cannot distinguish no activity from an unavailable export or an unintended filter. Show the reporting period and the source’s availability so the next reader has enough context to respond.

Finally, agree what an incomplete group should trigger. If orders exist but all their amounts are missing, the report has evidence of activity without a known total. Replacing that uncertainty with zero requires an explicit rule. A short explanation and a clear owner for exceptions help readers use the result without assigning it more certainty than its input supports.