asked on

Creating Total value in SQL query

I'm relatively new to SQL queries and have my basic query set up and am trying to add a total/sum line at the bottom of an automated report. But I keep getting the 'Error converting data type VARCHAR to numeric'. I've tried various combination of the CASE command but can't get it to work.

==========================================================

SELECT T1.[FirmName], T0.[ItemCode], T0.[ItemName], T2.[OnHand], T2.[WhsCode], T0.[IsCommited],T0.[OnOrder],T0.[LastPurPrc], T0.[LastPurPrc]* T0.[OnHand] as 'Value', T0.[LastPurCur] FROM OITM T0 INNER JOIN OMRC T1 ON T0.[FirmCode] = T1.[FirmCode] INNER JOIN OITW T2 ON T0.[ItemCode] = T2.[ItemCode] WHERE T1.[FirmName] ='Fill_in_Blank'
and T2.[OnHand] > 0

UNION ALL

Select '','','','','','','','Total',SUM(T0.[LastPurPrc]* T0.[OnHand]),''

FROM OITM T0 INNER JOIN OMRC T1 ON T0.[FirmCode] = T1.[FirmCode] INNER JOIN OITW T2 ON T0.[ItemCode] = T2.[ItemCode] WHERE T1.[FirmName] ='Fill_in_Blank'

==========================================================

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Brian Crowe

Typically this is something you would handle in the presentation layer (report generator, application, whatever)

How are you using this query?

Thomas_L_MTL

ASKER

Right now its in SAP Business one, and I user the Query Generator/Manager/scheduler. My goal is to have the report auto-emailed once a month in HTML format. I've already got it doing that (without total) but they really want the bottom line. otherwise I need to do this manually.

Thomas_L_MTL

ASKER

Everything above the UNION all is correct. It works without the bottom part.

Brian Crowe

BTW to answer your question Aneesh has already pointed you in the right direction. The datatype and name of each column is defined in the first query so the datatype of the corresponding column value in the second query must match. If T2[OnHand] (4th column) is an integer then you need to have a 0 or NULL in the 4th column of the second unioned query.

I would recommend replacing your empty strings with NULL's since it will work with any datatype.

Brian Crowe

After I looked at it a little closer:

You can't reference a table in the first clause from the second clause they are completely independent.

You are trying to insert the 'Total' label in the LastPurPrc column which I assume is numeric. It makes more sense to put it in the FirmName column which I assume is text.

Another option if you are interested is to add a running total column which would maintain the integrity of the dataset and still provide the data needed. What version of SQL Server are you running on?

This should at least be functional though (I took the liberty of aliasing your tables to something more intuitive than T0, T1, T2 for the sake of the next person that has to review your code):

WITH cte AS
(
	SELECT Manufacturer.[FirmName],
		Item.[ItemCode],
		Item.[ItemName],
		ItemWarehouse.[OnHand],
		ItemWarehouse.[WhsCode],
		Item.[IsCommited],
		Item.[OnOrder],
		Item.[LastPurPrc],
		Item.[LastPurPrc] * Item.[OnHand] as [Value],
		Item.[LastPurCur]
	FROM OITM AS Item
	INNER JOIN OMRC AS Manufacturer
		ON Item.[FirmCode] = Manufacturer.[FirmCode]
	INNER JOIN OITW AS ItemWarehouse
		ON Item.[ItemCode] = ItemWarehouse.[ItemCode]
	WHERE Manufacturer.[FirmName] ='Fill_in_Blank' 
		and ItemWarehouse.[OnHand] > 0
)
SELECT FirmName, ItemCode, OnHand, WhsCode, IsCommited, OnOrder, LastPurPrc, [Value], LastPurCur
FROM cte

UNION ALL

SELECT 'Total',	--FirmName
	NULL,		--ItemCode
	NULL,		--ItemName
	NULL,		--OnHand
	NULL,		--WhsCode
	NULL,		--IsCommited
	NULL,		--OnOrder
	NULL,		--LastPurPrc
	SUM(cte.[LastPurPrc] * cte.[OnHand]),	--Value
	NULL		--LastPurCur
FROM cte

Open in new window

Thomas_L_MTL

ASKER

This fixed it.. thx