Get subtotals SQL

[mamartin]

Can anyone tell me how to get subtotals by Street_Address for the OH_Qty and CM_Value columns in the following SQL:

SELECT
Sort_Order,
Facility_Quarters,
Street_Address,
Current_Status,
Item_Description,
Stock_Number,
OH_Qty,
CM_Value
FROM TM
ORDER BY
Sort_Order,
Facility_Quarters,
Street_Address,
Current_Status,
Item_Description,
Stock_Number

I need to "break" on Street_Address, but also need to have the Current_Status, Item_Description and Stock_Number columns print on the detail line. This is using SQL Server 2000. Any help? Much appreciate it.

Michael Martin

 

[JD1866DSC]

SELECT
Street_Address
,MAX(Current_Status) Current_Status
,MAX(Item_Description) Item_Description
,MAX(Stock_Number) Stock_Number
,SUM(OH_Qty) OH_Qty
,SUM(CM_Value) CM_Value
FROM
TM
GROUP BY
Street_Address
ORDER BY
Street_Address

I would do something similar to this, trouble is if you group on street address you may have different stock numbers per street address so which do you display?