SQL Query Totals Grouping Subtotals

A couple ideas:

SELECT  [SystemNum]
      ,[PurchaseDescription]
      ,[ModelNumber]
      ,[Brand]
      ,SUM([Quantity]) as Quantity
      ,SUM([Cost]) as Cost
      ,SUM(Quantity*Cost) AS 'Amount'

FROM [Equipment].[dbo].[SystemsComponents]
group by [SystemNum]
      ,[PurchaseDescription]
      ,[ModelNumber]
      ,[Brand]


SELECT [ID]
      ,[SystemNum]
      ,[PurchaseDescription]
      ,[ModelNumber]
      ,[Brand]
      ,[Quantity]
      ,[Cost]
      ,SUM(Quantity*Cost) 
         OVER (Partition by [SystemNum]) AS 'Amount'

FROM [Equipment].[dbo].[SystemsComponents]

Depends upon what you want to display on each row.

---

Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com