Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 230
  • Last Modified:

SQL Server Select SUM

I am getting error "datatype nvarchar(max) is invalid for sum...".  

SELECT SUM([ProductTaxonomy.csv].[YTD Sales Amount])
FROM [ProductTaxonomy.csv]
WHERE NOT EXISTS
(SELECT [Product Management.csv].[Product_Code] FROM [Product Management.csv] WHERE [ProductTaxonomy.csv].[Product Code] = [Product Management.csv].[Product_Code])
0
hojohappy
Asked:
hojohappy
  • 2
3 Solutions
 
PortletPaulCommented:
SELECT SUM( CAST([ProductTaxonomy.csv].[YTD Sales Amount] as decimal(18,4)) )
FROM [ProductTaxonomy.csv]
WHERE NOT EXISTS
(SELECT [Product Management.csv].[Product_Code] FROM [Product Management.csv] WHERE [ProductTaxonomy.csv].[Product Code] = [Product Management.csv].[Product_Code])

But this assumes that column is all numbers, & I have guessed that decimal(18,4) is appropriate
0
 
Anthony PerkinsCommented:
I would take it a step further:
SELECT  SUM(CASE ISNUMERIC([YTD Sales Amount])
              WHEN 1 THEN [YTD Sales Amount]
              ELSE 0
            END)
FROM    [ProductTaxonomy.csv] t
WHERE   NOT EXISTS ( SELECT 1
                     FROM   [Product Management.csv] m
                     WHERE  t.[Product Code] = m.Product_Code )

Open in new window


And yes, I understand that ISNUMERIC() is flawed at best, but it is better than nothing.
0
 
PortletPaulCommented:
of course, doh! what was I thinking - sorry
0
 
chaauCommented:
BTW, SQL Server has TRY_CAST (and TRY_CONVERT) that allow to avoid using CASES altogether:
SELECT  SUM(TRY_CAST([YTD Sales Amount] AS decimal(18,4)))
FROM    [ProductTaxonomy.csv] t
WHERE   NOT EXISTS ( SELECT 1
                     FROM   [Product Management.csv] m
                     WHERE  t.[Product Code] = m.Product_Code )

Open in new window

Note: they exist in SQL Server 2012 and above
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now