GROUP BY and HAVING for meaningful customer segments

GROUP BY collects rows into groups for aggregation. HAVING filters those groups using their aggregate results. WHERE filters the input rows before the grouped result is formed. That distinction decides whether a customer segment reflects the intended behaviour.

Suppose you want customers with at least two completed orders. You must first identify completed orders, then count them per customer, then retain customers whose count reaches two. Filtering large individual orders instead would answer a different question.

The commerce SQL lab provides a small synthetic dataset with repeated customers, cancelled orders and a missing customer record. The examples are executable in SQLite.

Define the behaviour, period and identity key

Our segment is “customer IDs appearing on at least two completed orders in the fixture.” It is not a claim that those customers are loyal, profitable or likely to buy again. Those stronger interpretations would require additional evidence.

In a production report, add an observation period and an identity rule. A customer who appears twice over ten years is different from one who purchases twice within a month. Merged accounts and guest checkouts can also change what a customer ID represents.

With that scope established:

SELECT
    customer_id,
    COUNT(*) AS completed_orders,
    SUM(order_total_paise) AS completed_value_paise
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING COUNT(*) >= 2
ORDER BY customer_id;

Expected output:

The status filter belongs in WHERE because it defines eligible rows. The count threshold belongs in HAVING because it describes a group. The SQL engine's documentation distinguishes these operations. SQLite SELECT.

See how filtering before grouping changes the segment

Imagine adding AND order_total_paise >= 12000 to WHERE. C001 now has only one eligible row because its other order is worth 10,000. C002 also has only one because its other order is 10,000. Neither passes the two-order threshold.

That query would find customers with at least two completed orders individually worth at least 12,000 paise. It would not find customers whose total completed-order value exceeds a threshold.

For the latter:

SELECT customer_id,
       COUNT(*) AS completed_orders,
       SUM(order_total_paise) AS completed_value_paise
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING SUM(order_total_paise) >= 25000
ORDER BY customer_id;

C002 qualifies with 28,000 paise across two orders. C004 qualifies with 25,000 from one order. Whether both belong in the desired segment depends on whether frequency is also part of the definition.

You can require both by combining aggregate conditions with AND. Write the plain-language segment definition beside the query so a reviewer can tell whether the combination is intentional.

Group by stable identifiers

Grouping by a customer name can merge different people who share a name and split one person whose spelling changes. Group by the stable business key, then enrich the result with a display label after checking the relationship.

Likewise, adding region to GROUP BY changes the grain to customer-region. That may be appropriate for location-specific activity, but it no longer guarantees one row per customer. Always name the output grain when changing the grouping columns.

Some SQL engines permit nonaggregated output columns that are not listed in GROUP BY, with engine-specific behaviour. Do not rely on an arbitrary display value from a group. Aggregate it deliberately or join it from a table with a verified one-to-one relationship.

Separate segmentation from enrichment

The order table includes C999, an unmatched customer ID. It has only one completed order, so it does not enter the repeat segment, but it should still appear in data-quality reporting.

If you INNER JOIN customer details before grouping, you exclude unmatched order records. That may change segment counts and value totals. A safer review sequence is to calculate behaviour at the order grain, verify the groups, and then explicitly decide how missing customer attributes affect the result.

The join lesson shows how an apparently harmless enrichment can remove 9,000 paise from this fixture.

Validate the segment as a set of rules

Check that every output ID meets all eligibility conditions. Inspect a customer just below each boundary, not only those who qualified. Reconcile the grouped totals to the eligible source population before applying HAVING, then document how much the segment excludes.

A useful exercise is to change the repeat threshold from two to three and predict the empty result before running the query. An empty segment can be correct. Avoid lowering the threshold only because a dashboard looks more impressive with more members.

When the segment drives an action, add a reason field or rule version. “Repeat purchasers under rule v1” is more reproducible than a permanent label such as “high value,” whose definition may change between teams.

NeuraPath's Data Analytics with Generative AI course covers SQL, statistics and business-facing reporting. Customer segmentation is a useful place to combine them: define the behaviour, calculate it correctly and avoid claiming more about the customer than the evidence supports.

Continue learning

This article is part of the SQL foundations for reliable analysis sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in One-to-many joins: reconcile revenue after a join.
• Continue with CASE WHEN: classify orders without overlapping buckets.