SQL Window Functions vs GROUP BY: Keep Detail Rows — sqlfmt

sqlfmt▍

SQL Window Functions vs GROUP BY: Keep Detail Rows — sqlfmt

Key takeaways

GROUP BY changes the result to one row per group.
Window functions retain detail rows and add a calculated value.
Use a subquery or CTE to filter a window result where QUALIFY is unavailable.

What changes in the result shape?

GROUP BY summarizes every customer into one row. A window function can calculate the same total while retaining every order, so the total remains beside the detail row.

Summary versus detail

-- one row per customer
SELECT customer_id, SUM(amount) AS customer_total
FROM orders
GROUP BY customer_id;

-- one row per order, with the customer total
SELECT customer_id, id AS order_id, amount,
       SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM orders;

PostgreSQL explains that window functions run after WHERE, GROUP BY, and HAVING and are available in SELECT and ORDER BY.

When should you use each?

Use GROUP BY for a true summary such as revenue by month. Use window functions for ranking, running totals, and calculations that need to remain beside each source row.

How do you filter a window result?

PostgreSQL does not allow a window function in WHERE or HAVING at the same level. Calculate it in a CTE or subquery, then apply an outer WHERE.

BigQuery provides a direct alternative through the QUALIFY clause.

Frequently asked questions

Does PARTITION BY group rows?

It defines the rows used by the window calculation but does not collapse them. The detail rows remain unless another query step removes them.

Can GROUP BY and window functions be combined?

Yes. In PostgreSQL, windows operate on grouped rows when a query uses GROUP BY or aggregates. Decide whether the calculation should see detail rows or summary rows.