CODEWITHSUNDEEP
sqlsql-servert-sql
asmgx
asmgx

Reputation: 8014

In temp table I get this error : Arithmetic overflow error converting float to data type numeric

This is my script

SELECT City, Country, Density, NULL AS CityAvg
INTO #Temp
FROM CityInfo


UPDATE T1
SET CityAvg = (SELECT STDEV(Density) FROM #Temp T2 WHERE T2.Country = T1.Country)
FROM #Temp T1

When I execute this I get this error

Arithmetic overflow error converting float to data type numeric.

I tried to change NULL to 0.00 but still same error

SELECT City, Country, Density, 0.00 AS CityAvg
INTO #Temp
FROM CityInfo


UPDATE T1
SET CityAvg = (SELECT STDEV(Density) FROM #Temp T2 WHERE T2.Country = T1.Country)
FROM #Temp T1

Data Sample

Country   City      Density
Australia Melbourne 23.365
Australia Sydney    25.657
Australia Perth     12.374
Canada    Toronto   27.748
Canada    Montreal  22.559

How to resolve this issue?

Note : This is a very simplified example of my code.

The actual code is more complex so I have to use #Temp table and it has to be in UPDATE statement

Upvotes: 0

Views: 484

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

This statement:

SELECT City, Country, Density, NULL AS CityAvg
INTO #Temp
FROM CityInfo;

has to assign a type to NULL. With no other information, SQL Server assigns it to the NULL type. That is presumably your problem. You can see this in the db<>fiddle.

So, cast this to the type you want:

SELECT City, Country, Density, CAST(NULL AS float) as CityAvg
INTO #Temp
FROM CityInfo;

Or if you want to copy the same type:

SELECT City, Country, Density, (case when 1=0 then Density end) AS CityAvg

Once you get that right, you can simplify your query by using window functions:

WITH toupdate AS (
      SELECT t.*,
             STDEV(Density) OVER (PARTITION BY city) as new_cityavg
      FROM #temp t
     )
UPDATE toupdate
    SET CityAvg = new_cityavg;

The window function should have better performance, because it will not repeat the calculation for each city.

Upvotes: 1

gotqn
gotqn

Reputation: 43636

The following code is working fine for me:

CREATE TABLE #DataSource
(
    [Country] VARCHAR(12) 
   ,[City] VARCHAR(12)
   ,[Density] DECIMAL(9, 3)
)

INSERT INTO #DataSource ([Country], [City], [Density])
VALUES ('Australia', 'Melbourne', 23.365)
      ,('Australia', 'Sydney', 25.657)
      ,('Australia', 'Perth',  12.374)
      ,('Canada', 'Toronto', 27.748)
      ,('Canada', 'Montreal', 22.559);



SELECT City, Country, Density, CAST(NULL AS DECIMAL(19, 2)) AS CityAvg
INTO #Temp
FROM #DataSource


UPDATE T1
SET CityAvg = (SELECT STDEV(Density) FROM #Temp T2 WHERE T2.Country = T1.Country)
FROM #Temp T1

SELECT *
FROM #Temp;

DROP TABLE #DataSource;
DROP TABLE #Temp;

Upvotes: 1

man shitan2
man shitan2

Reputation: 46

USE THIS

UPDATE T1
SET CityAvg = (SELECT STDEV(ISNULL(Population , 0)) FROM #Temp T2 WHERE T2.Country = T1.Country)
FROM #Temp T1

SELECT * FROM #Temp

Upvotes: -1

Related Questions