How to SUM total based on ItemID without using group by

Y.N 80 Reputation points
2024-01-22T02:11:41.1366667+00:00

Sample data like this:

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)

Need to calculate total qty of each ItemID. Tried with group by, but the result has only 3 rows. I need the original data details still in the final output. Also, I searched online and found query using OVER clause. And written this query:

select *,SUM(QTY)over(partition by ItemID order by ID rows unbounded preceding) as total 
from #demo

However, I want the total shown below in additional row of each ItemID, like this: User's image

Any suggestions are welcome.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,483 questions
0 comments No comments
{count} votes

Accepted answer
  1. LiHongMSFT-4306 30,751 Reputation points
    2024-01-22T02:41:10.4233333+00:00

    Hi @Y.N Try this query:

    SELECT ItemID,ISNULL(CAST(ID AS varchar(20)),'Total') AS ID ,SUM(QTY) AS QTY
    FROM #demo
    GROUP BY GROUPING SETS((ID,ItemID),ItemID)
    

    Best regards, Cosmog Hong

    0 comments No comments

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.