Could Cross Apply be used in this query?

Question

Here is a demo of my actual table:

create table #demo (ID int, ItemID int, QTY int)
insert into #demo values
(1,101,3),(2,102,1),(3,101,4),(4,102,2),(5,103,3)

I have written a query:

SELECT t2.ID,t2.QTY,t2.ItemID,t1.TotalQTY
FROM (SELECT ItemId,SUM(QTY) AS TotalQTY
      FROM #demo AS t1
      GROUP BY ItemId) AS t1
INNER JOIN #demo AS t2 ON t1.ItemId = t2.ItemID
ORDER BY ID;

Now I am wondering if I can use cross apply to simplify this query. Thanks in advance.

Answer 1

Hi @Y.N

if I can use cross apply to simplify this query

Yes, you can use Cross Apply here.

SELECT *
FROM #demo t1 CROSS APPLY(SELECT SUM(QTY) AS TotalQTY FROM #demo t2 WHERE t1.ItemId = t2.ItemID GROUP BY ItemId)C

If you want to simplify query, you could also try this:

SELECT *,SUM(QTY)OVER(PARTITION BY ItemID)AS TotalQTY
FROM #demo

Best regards, Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

	  SELECT ID,QTY,ItemID 	  ,SUM(QTY) Over(Partition BY ItemId) AS TotalQTY       FROM #demo 	  ORDER BY ID;