You can use GROUP BY SalesOrderID
. The difference is, with GROUP BY you can only have the aggregated values for the columns that are not included in GROUP BY.
In contrast, using windowed aggregate functions instead of GROUP BY, you can retrieve both aggregated and non-aggregated values. That is, although you are not doing that in your example query, you could retrieve both individual OrderQty
values and their sums, counts, averages etc. over groups of same SalesOrderID
s.
Here’s a practical example of why windowed aggregates are great. Suppose you need to calculate what percent of a total every value is. Without windowed aggregates you’d have to first derive a list of aggregated values and then join it back to the original rowset, i.e. like this:
SELECT orig.[Partition], orig.Value, orig.Value * 100.0 / agg.TotalValue AS ValuePercent FROM OriginalRowset orig INNER JOIN ( SELECT [Partition], SUM(Value) AS TotalValue FROM OriginalRowset GROUP BY [Partition] ) agg ON orig.[Partition] = agg.[Partition]
Now look how you can do the same with a windowed aggregate:
SELECT [Partition], Value, Value * 100.0 / SUM(Value) OVER (PARTITION BY [Partition]) AS ValuePercent FROM OriginalRowset orig