Jaco van der Laan

No Transformations in GROUP BY

11/09/2025  ·  Data Engineering

‹ ›

Do not place expressions/functions in GROUP BY. Compute all derived grouping keys (casts, DATE_TRUNC, COALESCE, CASE, math) upstream in named columns (CTEs), then GROUP BY those columns.

Why

  • Single source of truth for grouping logic (no duplicated formulas).
  • Easier to read, test, and change (you can inspect the pre-aggregation CTE).
  • Avoids subtle mismatches where the SELECT and GROUP BY use “almost the same” expression.
  • Query planners can optimize better when grouping columns are explicit.

Anti-patterns (transformations inside GROUP BY)

-- Casting inside GROUP BYSELECT CAST(order_ts AS DATE) AS order_date, SUM(revenue) AS total_revFROM bronze.ordersGROUP BY CAST(order_ts AS DATE);
-- Time bucketing in GROUP BY
SELECT DATE_TRUNC('month', event_ts) AS month_start, COUNT(*) AS events
FROM raw.events
GROUP BY DATE_TRUNC('month', event_ts);

-- Normalization in GROUP BY
SELECT UPPER(TRIM(country_code)) AS country, COUNT(*) AS users
FROM dim.users
GROUP BY UPPER(TRIM(country_code));

-- Arithmetic in GROUP BY
SELECT (quantity * unit_price) AS gross, COUNT(*) AS n
FROM fact.sales
GROUP BY (quantity * unit_price);

Best practice: derive first, then group

WITH orders_prepared AS (SELECTorder_id,customer_id,CAST(order_ts AS DATE) AS order_date, -- derive onceCAST(revenue AS NUMERIC) AS revenueFROM bronze.ordersWHERE order_ts IS NOT NULL)SELECTorder_date,SUM(revenue) AS total_revFROM orders_preparedGROUP BY order_date; -- group by named column, no expressions

Time bucketing (month) done upstream

WITH events_prepared AS (SELECTevent_id,CAST(event_ts AS TIMESTAMP) AS event_ts,DATE_TRUNC('month', CAST(event_ts AS TIMESTAMP)) AS month_start -- derive bucketFROM raw.eventsWHERE event_ts >= CURRENT_DATE - INTERVAL '365 days')SELECTmonth_start,COUNT(*) AS eventsFROM events_preparedGROUP BY month_start;

Normalization before grouping

WITH users_prepared AS (SELECTuser_id,CASEWHEN country_code IS NULL OR TRIM(country_code) = '' THEN 'UNKNOWN'ELSE UPPER(TRIM(country_code))END AS country_normFROM dim.users)SELECTcountry_norm,COUNT(*) AS usersFROM users_preparedGROUP BY country_norm;

Arithmetic / banding before grouping

WITH sales_prepared AS (SELECTsale_id,(quantity * unit_price) - COALESCE(discount_amount, 0) AS net_amount,CASEWHEN (quantity * unit_price) - COALESCE(discount_amount, 0) >= 1000 THEN 'HIGH'WHEN (quantity * unit_price) - COALESCE(discount_amount, 0) >= 200 THEN 'MEDIUM'ELSE 'LOW'END AS revenue_bandFROM fact.sales)SELECTrevenue_band,COUNT(*) AS n_orders,SUM(net_amount) AS total_netFROM sales_preparedGROUP BY revenue_band;

Works with other rules (pure UNION ALL + no transforms in GROUP BY)

WITH a_prepared AS (SELECTorder_id,customer_id,CAST(order_ts AS DATE) AS order_date,CAST(revenue AS NUMERIC) AS revenue,'A' AS _srcFROM src.orders_aWHERE order_ts >= DATE '2025-01-01'),
b_prepared AS (SELECTorder_id,customer_id,CAST(order_ts AS DATE) AS order_date,CAST(revenue AS NUMERIC) AS revenue,'B' AS _srcFROM src.orders_bWHERE order_ts >= DATE '2025-01-01'),combined_orders AS ( -- pure union: only aligned columns, no expressionsSELECT order_id, customer_id, order_date, revenue, _src FROM a_preparedUNION ALLSELECT order_id, customer_id, order_date, revenue, _src FROM b_prepared),agg AS ( -- derive any additional grouping keys here if neededSELECTcustomer_id,order_date,revenueFROM combined_orders)SELECTcustomer_id,order_date,SUM(revenue) AS total_revFROM aggGROUP BY customer_id, order_date; -- no expressions in GROUP BY

Common patterns moved before the GROUP BY

  • Type casting: CAST(ts AS DATE) AS order_date
  • Time bucketing: DATE_TRUNC('month', ts) AS month_start
  • Text normalization: UPPER(TRIM(code)) AS code_norm
  • Null handling: COALESCE(dim, 'UNKNOWN') AS dim_filled
  • Math/ratios: (a/b) AS ratio, (qty*price) AS gross
  • Categorization: CASE ... END AS bucket

Quick checklist

  • Create a CTE that defines every grouping key as a named column.
  • The final aggregation query should:
    • SELECT named grouping columns and aggregates,
    • GROUP BY the same named columns,
    • contain no functions/expressions in the GROUP BY.
  • Combine with the other rules:
    • Shift logic left → derive grouping keys early.
    • Exclude inline UNION ALL → use a named union CTE.
    • Keep UNION ALL pure → union only prepped columns; no transforms there.