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'

==========================================================
Thomas_L_MTLAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
Most likely the error is on the second statement, you actually need to run the both SELECT statements  without the UNION ALL statement. If you don't get any error,  error is with UNION ALL and the Second SELECT statement , in that case you need to replace the '' with 0 on appropriate places.
your second SELECT could look like this

Select '',0,'' ...  -- I put 0 on second column as it  denotes ItemCode which could be integer based on your first select
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Brian CroweDatabase AdministratorCommented:
Typically this is something you would handle in the presentation layer (report generator, application, whatever)

How are you using this query?
0
Thomas_L_MTLAuthor Commented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Thomas_L_MTLAuthor Commented:
Everything above the UNION all is correct. It works without the bottom part.
0
Brian CroweDatabase AdministratorCommented:
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.
0
Brian CroweDatabase AdministratorCommented:
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

0
Thomas_L_MTLAuthor Commented:
This fixed it.. thx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SAP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.