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)
We can do like this. Haven’t check the performance. :)
SELECT a.CustomerID, a.TotalDryItems, x.TotalValues
FROM [Sales].[Invoices] a
SELECT SUM(TotalDryItems) TotalValues
FROM [Sales].[Invoices] b
WHERE b.CustomerID = a.CustomerID
WHERE a.CustomerID < 31 AND a.OrderID < 5000
ORDER BY a.CustomerID
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:).