SQL group by and having for testers (2026)

TL;DR

GROUP BY creates one result row for each distinct grouping key, aggregate functions calculate facts inside that group, and HAVING removes groups after those facts exist. For testing, the essential habit is to state the expected grain first, then use WHERE for eligible detail rows and HAVING for defects such as duplicate keys, mismatched totals, or threshold breaches.

Key Takeaways

• Use GROUP BY to change row-level data into one result row per business group.
• Filter source rows with WHERE and filter calculated groups with HAVING.
• Define the business grain before selecting grouping columns or interpreting counts.
• Handle NULL, duplicate joins, and zero-row groups explicitly because each can distort QA evidence.
• Use conditional aggregation to compare multiple statuses or rules in one diagnostic query.
• Reconcile independent aggregates and investigate exceptions instead of treating a zero grand-total difference as proof.
• Read the execution plan and narrow data early when validation queries run against large environments.

SQL group by and having for testers is a practical way to turn thousands of database rows into focused QA evidence. GROUP BY answers questions such as how many orders each customer placed, while HAVING keeps only groups that violate a rule, such as an email appearing more than once or a paid order having no successful payment.

The syntax is short, but trustworthy validation requires more than memorizing clauses. A tester must define the business grain, choose the eligible source rows, understand NULL, prevent join multiplication, and know whether a query proves an invariant or merely suggests a problem. This guide develops those habits with runnable PostgreSQL examples that also use standard SQL wherever possible.

TL;DR

A reliable query can be read as a test statement: from eligible rows, create groups at a declared grain, calculate an expected fact, and return only groups that need attention.

1. SQL group by and having for testers: Start With Grain

Grain means what one row represents. Before writing SQL, complete this sentence: one result row should represent one _____. The answer might be one customer, one order, one product per day, or one tenant and status combination. That phrase determines the GROUP BY list.

Suppose orders contains one row per order. GROUP BY customer_id changes the result grain to one row per customer. Adding currency changes it again to one row per customer per currency. Neither query is inherently better. They answer different questions, and combining amounts across currencies would usually be a validation error.

Every selected expression must either identify the group or calculate a value from rows inside it. In portable SQL, this means a nonaggregated selected column belongs in GROUP BY. Some databases can infer functional dependencies in limited cases, but QA queries should remain explicit because clarity matters more than saving a column.

Grain also controls the meaning of a defect. HAVING COUNT(*) > 1 finds duplicate groups only relative to the chosen keys. Grouping by email tests email uniqueness. Grouping by tenant_id, email tests uniqueness within each tenant. If the product allows the same email in different tenants, the first query creates false positives.

Write the business constraint beside the query during review. For example: each tenant can have at most one active account per normalized email. That statement exposes missing dimensions such as tenant, lifecycle status, and normalization before a misleading count reaches a test report.

2. Create a Runnable QA Dataset

The examples use PostgreSQL and avoid extensions. Run this setup in a disposable database or transaction. The data deliberately includes one duplicate email, one partially paid order, one failed payment, and multiple currencies so the later queries have meaningful results.

DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  tenant_id INTEGER NOT NULL,
  email VARCHAR(200),
  status VARCHAR(20) NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
  status VARCHAR(20) NOT NULL,
  currency CHAR(3) NOT NULL,
  total_amount DECIMAL(10, 2) NOT NULL,
  created_at TIMESTAMP NOT NULL
);

CREATE TABLE payments (
  payment_id INTEGER PRIMARY KEY,
  order_id INTEGER NOT NULL REFERENCES orders(order_id),
  status VARCHAR(20) NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  provider VARCHAR(30) NOT NULL
);

INSERT INTO customers VALUES
  (1, 10, 'alex@example.test', 'active'),
  (2, 10, 'alex@example.test', 'active'),
  (3, 10, 'sam@example.test', 'active'),
  (4, 20, NULL, 'invited');

INSERT INTO orders VALUES
  (101, 1, 'paid', 'USD', 120.00, '2026-07-01 09:00:00'),
  (102, 1, 'paid', 'USD', 80.00, '2026-07-02 10:00:00'),
  (103, 2, 'pending', 'USD', 50.00, '2026-07-02 11:00:00'),
  (104, 3, 'paid', 'EUR', 75.00, '2026-07-03 12:00:00'),
  (105, 3, 'canceled', 'EUR', 25.00, '2026-07-04 13:00:00');

INSERT INTO payments VALUES
  (1001, 101, 'captured', 120.00, 'stripe'),
  (1002, 102, 'captured', 50.00, 'stripe'),
  (1003, 102, 'captured', 20.00, 'stripe'),
  (1004, 103, 'failed', 50.00, 'adyen'),
  (1005, 104, 'captured', 75.00, 'adyen');

These tables are intentionally small, but the reasoning scales. Do not copy production personal data into a test database. Use synthetic addresses, bounded permissions, and read-only credentials when querying shared environments. A runnable fixture is valuable because expected rows can be calculated before SQL is written, which gives the query its own oracle.

3. Understand Logical Query Processing Order

SQL is written as SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY, but its logical processing order is closer to FROM, WHERE, GROUP BY, HAVING, SELECT, and ORDER BY. This explains why a detail-row filter belongs in WHERE and why a group calculation can be filtered in HAVING.

Consider paid USD orders. First, FROM identifies orders. WHERE retains only rows whose status is paid and currency is USD. GROUP BY customer_id forms one group for each remaining customer. SUM(total_amount) calculates each total. HAVING SUM(total_amount) >= 100 keeps only customer totals that reach the threshold. Finally, ORDER BY sorts displayed results.

SELECT customer_id, SUM(total_amount) AS paid_usd_total
FROM orders
WHERE status = 'paid'
  AND currency = 'USD'
GROUP BY customer_id
HAVING SUM(total_amount) >= 100
ORDER BY customer_id;

For the fixture, customer 1 returns with 200.00. If status = 'paid' were moved from WHERE to a careless conditional or omitted, pending data could change the sum. If the threshold were placed in WHERE, the database could only compare individual order amounts, not the customer total.

Aliases are processed conceptually after HAVING, and alias availability in HAVING varies by database. Repeating the aggregate expression is more portable than HAVING paid_usd_total >= 100. A common table expression can remove repetition when the expression is complex, while also making the validation stages visible.

4. Use Aggregate Functions as Test Oracles

Aggregate functions compress many rows into a fact. COUNT(*) counts rows, including rows whose individual columns contain NULL. COUNT(column) counts only non-null values in that column. COUNT(DISTINCT column) counts unique non-null values. That difference is often central to data-quality testing.

SUM and AVG ignore NULL inputs, while an all-null group produces NULL, not zero. MIN and MAX identify bounds but do not prove all intermediate values are valid. A table with ages 1, 35, 200 has informative bounds, yet the aggregate alone does not explain which rule failed. Pair summary checks with exception queries.

The following profile exposes the invited customer whose email is missing:

SELECT
  tenant_id,
  COUNT(*) AS customer_rows,
  COUNT(email) AS populated_emails,
  COUNT(DISTINCT email) AS distinct_emails,
  MIN(customer_id) AS first_customer_id,
  MAX(customer_id) AS last_customer_id
FROM customers
GROUP BY tenant_id
ORDER BY tenant_id;

For tenant 20, customer_rows is 1 while populated_emails is 0. That is evidence of missing data, but whether it is a defect depends on the state model. An invited account may legitimately lack an email only if another identifier exists. SQL can expose the condition, while requirements determine the verdict.

Be cautious with averages. The average order value can stay constant while a damaging distribution shift occurs. For release validation, combine counts, sums, boundaries, and direct exception rows. Aggregates are compact test oracles, but a good oracle connects the number to an invariant and provides drill-down evidence.

5. WHERE vs HAVING in SQL Testing

WHERE filters individual rows before grouping. HAVING filters completed groups after aggregates are calculated. Use WHERE whenever the condition defines which source rows are eligible. Use HAVING when the condition depends on an aggregate or describes a property of the group.

A nonaggregate condition can sometimes appear in HAVING, depending on the grouped column and database. That does not make it a good default. Putting eligible-row conditions in WHERE states intent clearly and can reduce rows before grouping.

The dangerous case is moving a condition without considering meaning. WHERE total_amount > 100 followed by GROUP BY customer_id asks about customers with individual orders over 100. HAVING SUM(total_amount) > 100 asks about customers whose combined eligible orders exceed 100. Customer 1 in the fixture qualifies under the second condition because 120 + 80 = 200; a customer with two orders of 60 would also qualify only under the second.

For additional query design practice, use SQL interview questions for testers and explain each filter in business language before running it.

6. Detect Duplicate Business Keys Without Lying

Duplicate detection is the classic GROUP BY and HAVING use case, but it is easy to report false defects. Start with the exact uniqueness rule. In the fixture, the intended check is one active account per tenant and normalized email.

SELECT tenant_id, LOWER(TRIM(email)) AS normalized_email, COUNT(*) AS account_count
FROM customers
WHERE status = 'active'
  AND email IS NOT NULL
GROUP BY tenant_id, LOWER(TRIM(email))
HAVING COUNT(*) > 1;

This returns tenant 10 and alex@example.test with a count of 2. The WHERE clause excludes incomplete invited records. The group includes tenant scope and a basic normalization rule. Real products may define normalization differently. For example, automatically removing dots or plus tags from every email provider can merge distinct addresses and should not be invented in test SQL.

COUNT(*) > 1 proves multiple rows share the selected key. It does not prove which row is canonical, when duplication began, or whether soft-deleted versions are allowed. A second diagnostic query can join the duplicate keys back to detail rows so a defect report includes identifiers and states. Keep detection and investigation separate.

A database unique constraint is stronger prevention than a scheduled duplicate query, but constraints must match lifecycle rules. Partial unique indexes and generated normalized columns are vendor-specific design options. QA should test sequential and concurrent inserts through the application, then confirm the database rejects or safely resolves the race. A read query is evidence of current state, not proof that future duplicates are impossible.

7. Handle NULL and Empty Groups Deliberately

NULL means missing or unknown, not zero and not an empty string. In grouping, all null values for a grouping expression form one group. In most aggregates, null inputs are ignored. These rules can hide data-quality problems if the query author assumes every missing value disappears.

SELECT email, COUNT(*) AS rows_in_group
FROM customers
GROUP BY email
ORDER BY email NULLS LAST;

PostgreSQL supports NULLS LAST; other databases offer different ordering syntax. The query produces one group for the null email. By contrast, COUNT(email) for that group is zero. State which count you need. COUNT(*) answers how many customer rows exist, while COUNT(email) answers how many carry a value.

A related trap is zero-row groups. If no payments exist for an order, grouping only the payments table cannot produce an order group with a sum of zero. Start from orders, left join payments, and retain the outer row. Then use COUNT(payment_id) for the number of matches and COALESCE(SUM(...), 0) only if the business interpretation of no payments is truly zero.

Be careful where joined-table filters appear. A WHERE payments.status = 'captured' condition after a left join removes null-extended rows and effectively turns the result into an inner join. Put that eligibility condition in ON when orders without captured payments must remain visible. This issue is explored further in SQL joins for testers.

Do not blanket every aggregate with COALESCE. Converting unknown to zero can hide a broken feed. Decide whether no eligible rows, all null values, and a real numeric zero are equivalent under the requirement.

8. Group by Multiple Dimensions and Time Buckets

Production validation often needs more than one grouping column. A payment success report may require provider, currency, and calendar day. Each added dimension creates a finer grain and can expose a defect hidden by an overall total. For example, a stable global success rate can conceal that one provider is failing while another improves.

SELECT
  provider,
  status,
  COUNT(*) AS payment_count,
  SUM(amount) AS payment_amount
FROM payments
GROUP BY provider, status
ORDER BY provider, status;

Never sum monetary values across currencies unless conversion is part of the tested logic and a defined exchange rate is available. In the fixture, payment rows inherit currency through orders, so a monetary reconciliation should group by order or join currency and preserve it in the grain.

Time grouping also needs a declared zone. CAST(created_at AS DATE) uses the session interpretation for timestamp types and may not match a tenant's business day. PostgreSQL, SQL Server, MySQL, and cloud warehouses provide different time-zone and date-bucketing functions. Keep the query version-specific when boundaries matter, and test records just before and after midnight, daylight-saving transitions where applicable, and inclusive or exclusive range endpoints.

Avoid functions on indexed timestamp columns in the WHERE predicate when a half-open range expresses the same window. created_at >= '2026-07-01' AND created_at < '2026-08-01' is clearer about the upper boundary than an inclusive final timestamp. Grouping can format the retained rows afterward. For ETL and API checks, align the source and target windows before comparing aggregates.

9. Reconcile Orders and Payments at the Correct Grain

Reconciliation compares two independently derived facts that should agree. For each paid order, the sum of captured payments should equal the order total. The test must keep orders with no matching payment, ignore failed attempts, and avoid mixing order totals across payment rows.

SELECT
  o.order_id,
  o.currency,
  o.total_amount AS expected_amount,
  COALESCE(SUM(p.amount), 0) AS captured_amount
FROM orders AS o
LEFT JOIN payments AS p
  ON p.order_id = o.order_id
 AND p.status = 'captured'
WHERE o.status = 'paid'
GROUP BY o.order_id, o.currency, o.total_amount
HAVING COALESCE(SUM(p.amount), 0) <> o.total_amount
ORDER BY o.order_id;

Order 102 returns because captured payments total 70.00 while the expected amount is 80.00. The filter on payment status belongs in ON, preserving paid orders with no captured payments. Grouping includes the expected attributes, making the comparison unambiguous.

For approximate numeric types, exact equality may be unsafe. Monetary columns should normally use fixed-precision decimal types, as the fixture does. If the source uses floating-point measurements, compare according to a requirement-based tolerance and document why it is acceptable. Do not add an arbitrary tolerance to make failures disappear.

A matching grand total is weak evidence. An overpayment on one order can cancel an underpayment on another. Reconcile at the lowest stable business key, return exception rows, then optionally summarize exception count and amount. This layered approach supports both a release gate and a useful defect investigation. More extensive source-to-target techniques appear in validating data integrity with SQL.

10. Use Conditional Aggregation for Compact Diagnostics

Conditional aggregation calculates several related facts from one eligible row set. The portable pattern is SUM(CASE WHEN condition THEN 1 ELSE 0 END). It is especially useful for state distributions, rule coverage, and side-by-side comparisons. PostgreSQL also supports aggregate FILTER, but CASE travels across more databases.

SELECT
  provider,
  COUNT(*) AS attempts,
  SUM(CASE WHEN status = 'captured' THEN 1 ELSE 0 END) AS captured_count,
  SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) AS failed_count,
  SUM(CASE WHEN status = 'captured' THEN amount ELSE 0 END) AS captured_amount
FROM payments
GROUP BY provider
HAVING SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) > 0
ORDER BY provider;

The result returns only providers with a failed attempt while retaining total attempts and captured evidence. An ELSE 0 makes counting intent explicit. Without ELSE, unmatched rows yield NULL, and a group with no match can produce a null sum rather than zero.

Conditional aggregation can also encode invariants. A completed order group should have exactly one completion event, no cancellation event afterward, and at least one line item. Still, do not turn a query into an unreadable wall of CASE expressions. Name each metric after the rule, use a common table expression for complicated eligibility, and return identifiers for drill-down.

One scan can be efficient, but correctness comes first. If two metrics require different business populations, forcing them into one row set can create a denominator defect. State the population for every ratio. A failure rate based on all attempts is different from one based only on terminal attempts.

11. Prevent Join Multiplication and Misleading Counts

Joining two one-to-many relationships can multiply rows before aggregation. Suppose an order has three items and two payment attempts. Joining orders to both tables creates six combined rows for that order. SUM(payment.amount) then counts each payment three times, and COUNT(item_id) counts each item twice. The SQL runs successfully while the test oracle is wrong.

Detect multiplication by comparing COUNT(*), COUNT(DISTINCT order_id), and known relationship counts on a small fixture. Do not automatically replace every count with DISTINCT, because that can hide a faulty join and may not repair sums. Instead, pre-aggregate each many-side to the needed grain, then join those compact results.

WITH payment_totals AS (
  SELECT order_id, SUM(amount) AS captured_amount
  FROM payments
  WHERE status = 'captured'
  GROUP BY order_id
)
SELECT o.order_id, o.total_amount, COALESCE(pt.captured_amount, 0) AS captured_amount
FROM orders AS o
LEFT JOIN payment_totals AS pt ON pt.order_id = o.order_id
WHERE o.status = 'paid'
ORDER BY o.order_id;

This common table expression guarantees at most one payment summary row per order before the join. Apply the same method to item totals, shipment counts, or event facts. The approach makes the grain of each intermediate result reviewable.

A useful debugging technique is to remove aggregates temporarily and inspect the joined detail for one known identifier. If the same payment ID appears several times, determine which relationship produced the copies. SQL aggregation is not a cleanup operation. It should summarize a correct row set, not mask uncertainty about how that set was formed.

12. Practice SQL group by and having for testers With a Review Checklist

A repeatable review process makes aggregate SQL dependable under interview pressure and in release work. First, write the invariant and expected grain in plain English. Second, identify authoritative tables and relationship cardinality. Third, define eligible detail rows, including time window, tenant, lifecycle state, and soft deletion. Fourth, form groups and calculate facts. Fifth, use HAVING to return exceptions. Finally, reconcile the result against a hand-calculated fixture and inspect at least one detail path.

Review these questions before publishing a query:

1. What does one source row represent, and what should one result row represent?
2. Could a join duplicate facts before aggregation?
3. Does WHERE define eligibility and HAVING define group outcomes?
4. Are COUNT(*), COUNT(column), and COUNT(DISTINCT column) used intentionally?
5. What do null, missing groups, zero, and empty strings mean?
6. Are tenant, currency, time zone, and lifecycle state part of the key?
7. Does the exception query return enough identifiers to investigate safely?
8. Is the query read-only, bounded, parameterized by the test harness, and acceptable for the environment?

On large data, inspect the execution plan using the database's documented plan command. Indexes on filter and join keys can help, but aggregation may still require sorting or hashing. Restrict the time or tenant scope when the test permits it. Never add LIMIT to an exception query and then conclude no other defects exist. A limit changes observation, not underlying correctness.

Interview Questions and Answers

Q: What is the difference between WHERE and HAVING?

WHERE filters detail rows before groups and aggregates exist. HAVING filters groups after aggregate values are calculated. I use WHERE to define the eligible business population and HAVING to return groups that meet or violate an aggregate rule.

Q: Why must nonaggregated selected columns appear in GROUP BY?

A group can contain several source values, so the database needs one deterministic value for each result column. A grouping expression identifies the group, while an aggregate reduces its values. I keep QA queries explicit even if one database supports limited functional-dependency inference.

Q: How do you find duplicate emails?

I first define scope and normalization, then group by that business key and use HAVING COUNT(*) > 1. I usually filter allowed inactive or null states first. The returned count proves duplication relative to those rules, not which record should survive.

Q: What is the difference between COUNT(*) and COUNT(column)?

COUNT(*) counts rows. COUNT(column) counts rows where that expression is not null, and COUNT(DISTINCT column) counts unique non-null values. I choose among them based on whether I am testing row presence, field completeness, or uniqueness.

Q: Can HAVING be used without GROUP BY?

Many databases allow it because the eligible result can be treated as one implicit group. For clarity, I mainly use that form for a single aggregate assertion, such as checking whether the full eligible table has any duplicates. I verify portability if the query runs across engines.

Q: How do you avoid wrong sums after joins?

I map relationship cardinalities before aggregating. If several one-to-many tables are needed, I aggregate each to the target grain first and then join the summaries. DISTINCT is not a general repair for a multiplied sum.

Q: How would you validate a payment reconciliation?

I start from the authoritative order set, left join only captured payments, group at order and currency grain, and compare the captured sum with the fixed-precision expected total in HAVING. I return mismatched order IDs and test missing, partial, duplicate, failed, and overpayment cases.

Q: Why can an overall total hide defects?

Positive and negative differences can cancel. A grand total can match while individual customers or orders are wrong. I reconcile at the lowest stable business key, investigate exceptions, and summarize only after the detailed comparison is sound.

Common Mistakes

• Grouping by whatever columns satisfy a syntax error instead of declaring business grain.
• Using HAVING for ordinary row eligibility and making intent or performance worse.
• Using WHERE on the nullable side of a left join, which silently removes missing matches.
• Assuming COUNT(column) counts rows even when the column can be null.
• Summing money across currencies or days across unaligned time zones.
• Joining several one-to-many tables and aggregating the multiplied result.
• Using DISTINCT as a blanket fix without finding why duplicates exist.
• Converting every null aggregate to zero and hiding missing source data.
• Checking only a grand total, which lets offsetting errors cancel.
• Running an unbounded diagnostic on a shared production system without approval or a plan review.

Conclusion

SQL group by and having for testers turns database detail into targeted evidence when the query reflects the business model. Define one result row, filter eligible detail with WHERE, calculate group facts with intentional aggregate functions, and use HAVING to return exceptions. Then challenge the result with nulls, missing relationships, multiple currencies, time boundaries, and join cardinality.

Start with the provided fixture and predict every result by hand. Next, change one order to overpaid, add a customer in another tenant with the same email, and add an order with no payments. If your queries distinguish those cases without false positives, you are practicing the reasoning that makes SQL useful in real QA work.

Interview Questions and Answers

Frequently Asked Questions

Related Guides

• SQL for QA Tutorial for Beginners (2026)
• SQL for test data setup and teardown (2026)
• SQL Interview Questions for QA and Testers (2026)
• SQL joins for testers (2026)
• SQL subqueries for testers (2026)
• SQL window functions for testers (2026)