Calculations in SQL Expression

Good Morning-
I'm calculating ItemTotal  = Unit Cost*Qty

Now I need the sum of the Item Totals....your help will be greatly appreciated!!!!

Any way to create a cross tab in SQL? I so dislike doing this in EXCEL...currently, I've got an excel pivot table querying SQL with the below statement.

Regards,
Craig


SELECT 
	WO_95_UDF_WO_Header.ML_UDF_WOH_JOBNUMBER as [JOB#],
	WO_95_UDF_WO_Header.ML_UDF_WOH_CUST_NAME as [ CUSTOMER],
	WO_95_UDF_WO_Header.WorkOrder,
	WO1_WorkOrderMaster.WODate,
	IM1_SIZECODESORT_VIEW.Design AS [DESIGN],
	Substring(WO2_WorkOrderMaterialDetail.ComponentItemNumber,6,4) AS [BODY],
	IM1_InventoryMasterfile.Category2 AS [COLOR],
	IM1_SIZECODESORT_VIEW.SizeCode,
	WO_95_UDF_WO_Header.DB_UDF_WOH_HITS AS [HITS],
	WO2_WorkOrderMaterialDetail.ExtdQtyRequired AS [QTY],
	WO_95_UDF_WO_Header.ML_UDF_WOH_PRICE AS [PRICE],
	WO_95_UDF_WO_Header.ML_UDF_WOH_ADDONCHARGE AS [ADD'L],
	WO_95_UDF_WO_Header.ML_UDF_WOH_PRICE+  WO_95_UDF_WO_Header.ML_UDF_WOH_ADDONCHARGE AS [UNIT PRICE],
	(WO_95_UDF_WO_Header.ML_UDF_WOH_PRICE+  WO_95_UDF_WO_Header.ML_UDF_WOH_ADDONCHARGE)*WO2_WorkOrderMaterialDetail.ExtdQtyRequired AS [ITEM TOTAL]
	
FROM WO1_WorkOrderMaster
 
 INNER JOIN WO_95_UDF_WO_Header 
         ON 
               WO1_WorkOrderMaster.WorkOrder = WO_95_UDF_WO_Header.WorkOrder 
 INNER JOIN IM1_InventoryMasterfile
 INNER JOIN WO2_WorkOrderMaterialDetail 
         ON 
               IM1_InventoryMasterfile.ItemNumber = WO2_WorkOrderMaterialDetail.ProductProduced 
         ON
               WO_95_UDF_WO_Header.WorkOrder = WO2_WorkOrderMaterialDetail.WorkOrder  
 INNER JOIN IM1_SIZECODESORT_VIEW 
         ON 
               IM1_InventoryMasterfile.ItemNumber = IM1_SIZECODESORT_VIEW.ItemNumber
ORDER BY WO_95_UDF_WO_Header.WorkOrder

Open in new window

TestData.xls
oceansupportAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Alpha AuConnect With a Mentor Commented:
You mean you need a running total in the result set?

You may refer to this.

http://www.databasejournal.com/features/mssql/article.php/3112381
0
 
oceansupportAuthor Commented:
BTW....this is MS SQL 2000!!!
0
 
oceansupportAuthor Commented:
Absolutely....thank you!!!
0
All Courses

From novice to tech pro — start learning today.