In my opinion, it seems important to me to explain why, due to the need for GROUP BY in your SQL when summing with the OVER () clause, and why you get duplicate rows of data when you expect one row per BrandID.
Take this example: you need to aggregate the total selling price of each order line, depending on a specific order category, between two dates, but you also need to save the data of individual orders in your final results. The SUM () column in the SalesPrice column will not allow you to get the correct totals because it requires the GROUP BY group, therefore discarding the details because you cannot save individual order lines in the select statement.
Many times we see the #temp table, the @table or CTE variable, filled with the sum of our data and grouped so that we can join it later to get a column of the required amounts. This can add processing time and extra lines of code. Instead, use OVER (PARTITION BY ()) as follows:
SELECT OrderLine, OrderDateTime, SalePrice, OrderCategory, SUM(SalePrice) OVER(PARTITION BY OrderCategory) AS SaleTotalPerCategory FROM tblSales WHERE OrderDateTime BETWEEN @StartDate AND @EndDate
Please note that we do not group, and we have a separate column of order lines. PARTITION BY in the last column returns us the total sales price for each row of data in each category. As the last column says, we want the sum of the sale price (SUM (SalePrice)) over the section of my results and from the specified category (OVER (PARTITION by CategoryHere)).
If we remove other columns from our select statement and leave our final column SUM (), do the following:
SELECT SUM(SalePrice) OVER(PARTITION BY OrderCategory) AS SaleTotalPerCategory FROM tblSales WHERE OrderDateTime BETWEEN @StartDate AND @EndDate
The results will still repeat this amount for each row in our original result set. The reason for this method does not require GROUP BY. If you do not need to save separate line data, simply SUM () without using OVER () and group the data accordingly. Again, if you need an extra column with specific totals, you can use the OVER (PARTITION BY ()) method described above without additional selections to join.
The foregoing is only explained by why it gets duplicate lines of the same number and helps to understand what this sentence gives. This method can be used in many ways, and I highly recommend continuing reading from the documentation here:
Over clause