SQL query help!

Hi,
I'm trying to run a query to SUM the results of a case statement.  Below is the query; it pulls results just fine, but when I put a 'SUM' around the case statement, I get the following error:

"Operand data type nvarchar is invalid for sum operator."

The column type I'm doing the sum against is nvarchar(200).

I've tried to convert, but not sure I'm doing it right.  All the values in the table (at least for the range I'm running) are actual values (5814.000, 405, 11802, 686.31 ,etc)

Here's the script if you'd like to see it.  Basically there are two data values in a table - machine weight and forge weight.  the case statement is picking the value of the machine weight ('d') unless there's no value, in which case it uses forge weight ('e')

      
select
      --a.EndTime,
      --a.ComplQuantity,
      --b.UsrDfnId as 'Workorder',
      --b.DrawName,
      --c.UsrDfnId as 'Operation',
      --d.DataValue as 'Machine Weight',
      --e.DataValue as 'Forge Weight',
      case when d.DataValue IS NULL then e.DataValue
      else e.DataValue end as 'Weight'
from COSS_WorkOrderOprtHistDetail a
left outer join coss_workorder b
on a.WorkOrderIdent = b.ident
left outer join COSS_Operation c
on a.OprtIdent = c.ident
left outer join COSS_CustomAttributeValue d
on b.UsrDfnId = d.OwnerUsrDfnId and d.AttribName = 'Machine Weight'
left outer join COSS_CustomAttributeValue e
on b.UsrDfnId = e.OwnerUsrDfnId and e.AttribName = 'Forge Weight'
where a.EndTime between '2015-07-05' and '2015-07-11' and b.CustomerName not like 'Internal%' and c.UsrDfnId = 'FORGE' and a.ComplQuantity > '0'
order by b.UsrDfnId
Mystical_IceAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Use ISNULL(d.DataValue, e.DataValue) instead of the CASE, which, BTW, is always returning e.DataValue because of a typeo ;-).
Then you'll have to CAST or CONVERT to float, and then sum it up.
Lastly, to apply an aggregate like COUNT, MIN, MAX, SUM, AVG, you need to GROUP BY any other column not contained in those functions.
select 
      --a.EndTime,
      --a.ComplQuantity,
      --b.UsrDfnId as 'Workorder',
      --b.DrawName,
      --c.UsrDfnId as 'Operation',
      --d.DataValue as 'Machine Weight', -- this would have to be converted and sum'ed up the same!
      --e.DataValue as 'Forge Weight', -- this would have to be converted and sum'ed up the same!
      sum(convert(float, isnull(d.DataValue, e.DataValue))) as 'Weight'
from COSS_WorkOrderOprtHistDetail a
left outer join coss_workorder b
on a.WorkOrderIdent = b.ident
left outer join COSS_Operation c
on a.OprtIdent = c.ident
left outer join COSS_CustomAttributeValue d
on b.UsrDfnId = d.OwnerUsrDfnId and d.AttribName = 'Machine Weight'
left outer join COSS_CustomAttributeValue e
on b.UsrDfnId = e.OwnerUsrDfnId and e.AttribName = 'Forge Weight'
where a.EndTime between '2015-07-05' and '2015-07-11' and b.CustomerName not like 'Internal%' and c.UsrDfnId = 'FORGE' and a.ComplQuantity > '0'
-- group by   a.EndTime, a.ComplQuantity, b.UsrDfnId, b.DrawName, c.UsrDfnId
order by b.UsrDfnId

Open in new window

sameer2010Commented:
Hi,
Should the following be modified from above?
case when d.DataValue IS NULL then e.DataValue
       else d.DataValue end as 'Weight'

Open in new window


Also, assuming that you have maximum 3 decimal points and upto 7 MSB, you can write
select sum(cast(Weight as decimal(10,3))) from yourQuery

Open in new window

PortletPaulEE Topic AdvisorCommented:
You can continue to use a case expression, or you can use ISNULL(), or you could use COALESCE()
These produce the same result:

case when d.DataValue IS NULL then e.DataValue else d.DataValue end

ISNULL(d.DataValue,e.DataValue)

COALESCE(d.DataValue,e.DataValue)
ISNULL() is specific to SQL Server
the other options can be used in SQL Server and other databases also.


Then, because the data is stored as as a string type (NVARCHAR) it cannot be summed unless it is converted to a numeric type first. You can choose the level of numeric precision, e.g. as suggested above it could be float or decimal with 3 decimal digits, or choose a precision to suit which might even be integer. Then it can be summed.

SUM( CONVERT(  [numeric type here] , [choose an option, e.g. ISNULL(d.DataValue,e.DataValue)]   ) )

NOTE.
IF any of the data in d.DataValue/e.DataValue cannot be converted to a number then you will get an error.

In SQL Server you can use ISNUMERIC() to guard against such errors

E.G.
SUM(
      CASE WHEN ISNUMERIC(ISNULL(d.DataValue,e.DataValue) = 1
          THEN ISNULL(d.DataValue,e.DataValue)
          ELSE 0
      END
      )

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
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
Query Syntax

From novice to tech pro — start learning today.