Aggregates versus window calculations

A report may need one total per category, or it may need each original row with its category total attached. Both queries use SUM, but they return different row shapes. Reading GROUP BY and window functions through that distinction helps prevent totals from being counted twice by the consuming screen.

Choose the grain of the output

Category A contains amounts ten and twenty; B contains five. GROUP BY returns two category rows. SUM OVER preserves all three detail rows, attaching thirty to both A rows and five to B. The following VALUES-based example can be run in SQL Server or PostgreSQL without application tables.

WITH Items AS (
 SELECT * FROM (VALUES ('A',10),('A',20),('B',5)) d(Category,Amount)
)
SELECT Category,Amount,
 SUM(Amount) OVER(PARTITION BY Category) AS CategoryTotal
FROM Items ORDER BY Category,Amount;

SELECT Category,SUM(Amount) AS CategoryTotal
FROM (VALUES ('A',10),('A',20),('B',5)) d(Category,Amount)
GROUP BY Category ORDER BY Category;

The first result is A/10/30, A/20/30 and B/5/5. The second is A/30 and B/5. CategoryTotal is information about the row's group, not another row amount. Summing it again in the client produces sixty for A. A value repeated alongside several records is not necessarily an additive measure at that detail level.

A window total annotates the existing rows

PARTITION BY Category identifies the rows used in the calculation. With no ORDER BY inside this window, SUM covers the entire partition. That is useful when an original record needs its group's context for a ratio or comparison. When the requirement is simply a category summary, GROUP BY describes the reduction directly.

Adding window ordering can change the calculation

An ORDER BY inside OVER is not merely a presentation change. Aggregate window functions acquire a frame, and the result can become an accumulating total rather than the total for the whole category. Equal ordering values can also interact with the default RANGE frame. This explains why adding a date order may unexpectedly change totals.

SUM(Amount) OVER (
    PARTITION BY Category
    ORDER BY Id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS RunningTotal

This fragment explicitly defines a running total for a dataset that has an Id column. It is not a complete standalone query to paste unchanged into the first example. To repeat the entire category total on every row, leave the window unordered or deliberately select a frame spanning the whole partition.

Separate visible detail from the aggregation scope

WHERE reduces rows before the window is calculated. Showing only amounts of at least twenty while retaining the full-period total requires separate calculation and display scopes. A query can execute correctly and still answer the wrong reporting question if those scopes are not named.

Join multiplication affects window calculations too. If one business record is repeated for three attached documents, the amount may be counted three times before either kind of aggregation runs. Establish the intended row grain first. Appending DISTINCT later does not always recover the original business meaning.

Make the consumer aware of each measure

Names such as Amount, CategoryTotal and RunningTotal keep different measures distinct in UI and export code. Test empty categories, null amounts and equal ordering values. Verify expected row count and numeric totals together; a coincidentally correct final sum is weaker evidence than a result whose shape and meaning both match the requirement.

PostgreSQL window functions; T-SQL OVER and window frames.