SQL Query Fundamentals Questions

Whether a SELECT alias can be reused in GROUP BY or ORDER BY differs by engine: MySQL and PostgreSQL both generally allow it, SQL Server does not permit it in GROUP BY (though it does in ORDER BY); writing portable SQL means not relying on the permissive behavior.

Structured elaboration

-- Works in PostgreSQL and MySQL
SELECT DATE_TRUNC('month', ts) AS month, COUNT(*) FROM events GROUP BY month ORDER BY month;
-- Portable across all three (repeats the expression instead of the alias)
SELECT DATE_TRUNC('month', ts) AS month, COUNT(*)
FROM events GROUP BY DATE_TRUNC('month', ts) ORDER BY DATE_TRUNC('month', ts);

The reason engines differ comes down to when aliases are considered "in scope": logically, GROUP BY is evaluated before the SELECT list assigns aliases, so strictly by the standard SQL execution order, the alias shouldn't exist yet when GROUP BY runs. PostgreSQL and MySQL extend the standard to allow it anyway as a convenience; SQL Server follows the stricter interpretation for GROUP BY.

Worked example

A query grouping by a computed month expression will run unmodified on PostgreSQL and MySQL using the alias, but needs the full expression repeated in GROUP BY to run on SQL Server without a syntax error.

Trade-offs and pitfalls

If the SQL only ever runs on one known engine, use whichever form that engine's team finds most readable. If the query needs to be portable (a shared library, a multi-engine analytics tool, or simply not knowing the eventual target), always repeat the expression rather than relying on alias reuse, since that form works everywhere without exception.

Comparing or grouping values that look numerically identical but are stored with different types or representations, a numeric string '1' versus '01' versus '1.0', can produce more groups than intended, because GROUP BY compares the stored representation, not a normalized numeric interpretation of it.

Structured elaboration

-- Grouping on the raw text produces 3 separate groups
SELECT k, COUNT(*) FROM t GROUP BY k;
-- Standardizing to a numeric type first collapses them correctly
SELECT CAST(k AS REAL) AS k_num, COUNT(*) FROM t GROUP BY k_num;

If a column meant to represent a number is stored as TEXT, the values '1', '01', and '1.0' are three genuinely different strings, and GROUP BY on the raw text treats them as three separate groups, even though they represent the identical number. Casting to a genuine numeric type before grouping standardizes all three to the same underlying value, collapsing them correctly into one group.

Worked example

Given a text column with values '1', '01', '1.0': grouping on the raw text produces three separate rows, each with count 1. Casting to REAL first and grouping on the cast value produces one row (1.0) with count 3, correctly recognizing all three as the same number.

Trade-offs and pitfalls

The general lesson: standardize the TYPE of a grouping key at the point it's first captured or loaded (store numbers as numbers, not as text), rather than patching every downstream query with a CAST; a CAST-in-every-query workaround is a sign the underlying schema or ingestion pipeline has a type-modeling gap that would be worth fixing at the source. Floating-point columns specifically carry a second, related but distinct risk: two values that are mathematically equal can still differ in their last few bits of binary representation, requiring a ROUND (not a CAST) to standardize before grouping. For example, two rows computed as 0.1 + 0.2 and a literal 0.3 are mathematically equal, but under IEEE 754 double-precision floating point (the standard representation most engines use for FLOAT/REAL/DOUBLE columns), 0.1 + 0.2 is actually stored as 0.30000000000000004, one bit off from the literal 0.3.

-- Grouping on the raw float value produces 2 groups instead of 1
SELECT k, COUNT(*) FROM t GROUP BY k;
-- Rounding to a fixed number of decimal places collapses them correctly
SELECT ROUND(k, 2) AS k_rounded, COUNT(*) FROM t GROUP BY k_rounded;

Given t(k) with the two rows above: grouping on the raw k produces two separate rows, each with count 1 (0.3 and 0.30000000000000004 are treated as different group keys). Grouping on ROUND(k, 2) instead produces one row, 0.3 with count 2, correctly recognizing both as the same number once rounded to a sane precision. A plain CAST doesn't fix this the way it fixed the string-versus-numeric case: casting a float to another float type doesn't remove the representation error, only ROUND (or an equivalent fixed-precision truncation) does, because it deliberately discards the bits beyond the chosen precision rather than just changing the storage type.

GROUPING() (or the combined GROUPING_ID() in engines that support it) returns 1 for a grouping column that's been rolled up to NULL as part of a subtotal, and 0 when that column holds a real, detail-level value, letting you reliably tell a genuine NULL apart from a ROLLUP-generated one.

Structured elaboration

SELECT category, subcategory, SUM(amount) AS total_amount,
  CASE
    WHEN GROUPING(category) = 1 THEN 'grand_total'
    WHEN GROUPING(subcategory) = 1 THEN 'category_total'
    ELSE 'detail'
  END AS level_label
FROM sales
GROUP BY ROLLUP(category, subcategory);

Without GROUPING(), a NULL in the subcategory column of the result is ambiguous: it might be a genuine data value (if subcategory itself can legitimately be NULL) or it might be ROLLUP's signal for "this row sums across all subcategories". GROUPING(subcategory) resolves the ambiguity directly: 1 always means "rolled up here", regardless of whether the underlying data ever has a genuine NULL subcategory.

Worked example

Given sales(category, subcategory, amount) with rows (a,x,10), (a,y,20), (b,x,5): the detail level shows category='a', subcategory='x', total=10, level='detail' (and similarly for the other two detail combinations); the category-subtotal level shows category='a', subcategory=NULL, total=30 (10+20 combined), level='category_total', and a second subtotal row for category='b', subcategory=NULL, total=5 (the single b/x row rolled up on its own, since it has no other subcategory to combine with); the grand-total level shows category=NULL, subcategory=NULL, total=35 (30 from category 'a' plus 5 from category 'b'), level='grand_total'. In total, ROLLUP(category, subcategory) over these 3 detail rows produces 6 output rows: 3 detail + 2 category subtotals (one per distinct category) + 1 grand total.

Trade-offs and pitfalls

GROUPING_ID() (a single combined value encoding which columns were rolled up, as a bitmask) is more convenient than checking each GROUPING() call separately once there are more than two or three grouping columns, though not every engine supports it identically; check your specific engine's documentation before assuming syntax parity with another.

Filtering to completed orders belongs in WHERE (a per-row condition, evaluated before aggregation); requiring at least 100 such orders per vendor belongs in HAVING (a per-group condition on the aggregate count), and mixing them up produces a subtly wrong number rather than an error.

Structured elaboration

SELECT v.vendor_id, v.name, AVG(o.amount) AS avg_order_value, COUNT(*) AS n
FROM orders o
JOIN vendors v ON v.vendor_id = o.vendor_id
WHERE o.status = 'completed'
GROUP BY v.vendor_id, v.name
HAVING COUNT(*) >= 100;

Putting the status filter in HAVING instead of WHERE would still produce a result, but the AVG and COUNT would be computed over ALL orders (completed and not), then filtered by a HAVING condition that's checking the wrong thing entirely; the two clauses aren't interchangeable substitutes even though both look like "filtering". This exact WHERE-then-HAVING combination (filter raw rows, then threshold the aggregate) is one of the most common realistic SQL patterns in business reporting: exclude bad/irrelevant rows first, then only surface groups with enough volume to be statistically meaningful.

Worked example

Given vendor 1 with orders of 100 (completed), 200 (completed), and 50 (refunded), and vendor 2 with a single 300 (completed) order: filtering to completed orders first, vendor 1's average is (100+200)/2 = 150 across 2 qualifying orders; vendor 2 has only 1 completed order. If the HAVING threshold were COUNT(*) >= 2, vendor 1 passes (2 completed orders) and vendor 2 is filtered out (only 1). The same pattern generalizes directly to the "at least 100 orders" real threshold in the question, just with a larger dataset.

Trade-offs and pitfalls

The same skill applies to several close variants of this pattern: minimum-order-count thresholds, minimum-transaction-count thresholds, and duplicate-detection HAVING counts are all the identical WHERE-then-HAVING shape with a different business label attached. Recognizing the shape matters more than memorizing any one instance of it.

Joining a customers table directly to order_items and summing produces an inflated total whenever a customer has more than one order, because each order's line items get re-multiplied by however many order rows are being joined through; pre-aggregating order_items to order-level totals first, then joining up, avoids the inflation.

Structured elaboration

-- WRONG shape (works fine for a single order, but is a latent bug)
SELECT c.id, SUM(oi.qty * oi.unit_price) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY c.id;

-- SAFE shape: pre-aggregate order_items to order totals, then join up
WITH order_totals AS (
  SELECT order_id, SUM(qty * unit_price) AS order_revenue
  FROM order_items GROUP BY order_id
)
SELECT c.id, COUNT(o.order_id) AS order_count, SUM(ot.order_revenue) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_totals ot ON ot.order_id = o.order_id
GROUP BY c.id;

The bug in the first shape isn't in the SUM itself, it's that the join produces one row per (order, order_item) pair, and a customer-level COUNT or a mis-scoped aggregate over that joined result silently reflects the item-level grain, not the order-level or customer-level grain you're mentally picturing.

Worked example

A customer with one order containing two line items ($100 and $30, order total $130): a naive COUNT(o.order_id) after joining through order_items returns 2 (one count per line item), not 1 (the actual number of orders), even though SUM(qtyunit_price) happens to still total correctly to $130 in this single-order case. Pre-aggregating order_items to order totals first, then joining, correctly reports order_count = 1 and revenue = $130. The risk compounds with more orders: any customer-level COUNT taken directly off the item-joined result will always be inflated by however many items each order has, while SUM(qtyunit_price) itself stays numerically correct only because multiplication and summation happen to commute here, a coincidence that doesn't extend to non-additive metrics like COUNT or AVG taken at the wrong grain.

Trade-offs and pitfalls

The general rule: whenever a query joins through a one-to-many (or many-to-many) relationship, ask explicitly "what does one row of this joined result actually represent" before writing any aggregate on top of it, and pre-aggregate to the coarser grain BEFORE joining up to an even coarser one, rather than aggregating across every level in a single pass.