[sql] SUM OVER PARTITION BY

In my opinion, I think it's important to explain the why behind the need for a GROUP BY in your SQL when summing with OVER() clause and why you are getting repeated lines of data when you are expecting one row per BrandID.

Take this example: You need to aggregate the total sale price of each order line, per specific order category, between two dates, but you also need to retain individual order data in your final results. A SUM() on the SalesPrice column would not allow you to get the correct totals because it would require a GROUP BY, therefore squashing the details because you wouldn't be able to keep the individual order lines in the select statement.

Many times we see a #temp table, @table variable, or CTE filled with the sum of our data and grouped up so we can join to it again later to get a column of the sums we need. This can add processing time and extra lines of code. Instead, use OVER(PARTITION BY ()) like this:

SELECT
  OrderLine, 
  OrderDateTime, 
  SalePrice, 
  OrderCategory,
  SUM(SalePrice) OVER(PARTITION BY OrderCategory) AS SaleTotalPerCategory
FROM tblSales 
WHERE OrderDateTime BETWEEN @StartDate AND @EndDate

Notice we are not grouping and we have individual order lines column selected. The PARTITION BY in the last column will return us a sales price total for each row of data in each category. What the last column essentially says is, we want the sum of the sale price (SUM(SalePrice)) over a partition of my results and by a specified category (OVER(PARTITION BY CategoryHere)).

If we remove the other columns from our select statement, and leave our final SUM() column, like this:

SELECT
  SUM(SalePrice) OVER(PARTITION BY OrderCategory) AS SaleTotalPerCategory
FROM tblSales 
WHERE OrderDateTime BETWEEN @StartDate AND @EndDate

The results will still repeat this sum for each row in our original result set. The reason is this method does not require a GROUP BY. If you don't need to retain individual line data, then simply SUM() without the use of OVER() and group up your data appropriately. Again, if you need an additional column with specific totals, you can use the OVER(PARTITION BY ()) method described above without additional selects to join back to.

The above is purely for explaining WHY he is getting repeated lines of the same number and to help understand what this clause provides. This method can be used in many ways and I highly encourage further reading from the documentation here:

Over Clause