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.

SQL SERVER - Adding Values WITH OVER and PARTITION BY oversum

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.

SQL SERVER - Adding Values WITH OVER and PARTITION BY oversum1

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)

, , , ,
Previous Post
SQL SERVER – How to DELETE Multiple Table Together Via SQL Server Management Studio (SSMS)?
Next Post
SQL SERVER – Save Auto Recover Information – SSMS

Related Posts

2 Comments. Leave new

  • Pawan Kumar Khowal
    June 16, 2018 10:47 am

    We can do like this. Haven’t check the performance. :)

    SELECT a.CustomerID, a.TotalDryItems, x.TotalValues
    FROM [Sales].[Invoices] a
    CROSS APPLY
    (
    SELECT SUM(TotalDryItems) TotalValues
    FROM [Sales].[Invoices] b
    WHERE b.CustomerID = a.CustomerID
    )x
    WHERE a.CustomerID < 31 AND a.OrderID < 5000
    ORDER BY a.CustomerID

    Pawan

    Reply
  • If one assumes “without GROP BY” really means “I cannot get the results I want when using GROUP BY”, then using a derived table or a CTE that does a GROUP BY, and a JOIN it back to the a DISINCT of the original table, the OP will get the expected results. But, my response could ”bruise an ego”, so perhaps itnisnbetter to do as the OP requested (no matter how irrational the posit may be).

    Of course, I have not verified the execution plans are identical, so my ego may be bruised:).

    Whether PARTITION or GROUP BY is used, there is a valuable lesson:).

    Reply

Leave a Reply

Menu