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
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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:
Any suggestions are welcome.
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