SQL SERVER – Adding Values WITH OVER and PARTITION BY

Once in a while, I get questions where users have indeed explained the question well enough that I can easily understand their question. If I understand question quickly, I am always able to answer it efficiently as well. In this blog post, we will see one such question which I answered with keywords WITH OVER and PARTITION BY.

Let us see the question here:

How do I sum values for each group without using GROUP BY in my query? I want sum based on the one column but I do not want a sum of entire results. See the following image for additional explanation.

You can clearly see what the user wanted to do. He wanted to group by all the items based on the customer. He had sent me a query as well to look at. Here is the query which I have simplified and built over the sample database WorldWideImports.

SELECT CustomerID, TotalDryItems
FROM [Sales].[Invoices]
WHERE CustomerID < 31 AND OrderID < 5000
ORDER BY CustomerID

Now let us modified above query to get necessary grouping and sum over the CustomerID as requested.

Here is the query which I have a build which also includes keywords WITH OVER and PARTITION BY.

SELECT CustomerID, TotalDryItems,
SUM(TotalDryItems) OVER(PARTITION BY CustomerID) TotalValues
FROM [Sales].[Invoices]
WHERE CustomerID < 31 AND OrderID < 5000
ORDER BY CustomerID

When you run above query it will give us result with necessary sum value and to build this we have actually not used the query GROUP BY.

Let me know if you know any other alternative solutions and I will be super excited to share on the blog with due credit to you.

Reference: Pinal Dave (https://blog.sqlauthority.com)