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

Question

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

Is your #Temp table created via a separate DDL or on-the-fly based on the SELECT INTO? What is the data type of the field in which you are trying to store the CityAvg value? What if you explicitly cast your CityAvg calculation, something like: CAST(CityAvg AS DECIMAL(38,5)? — ravioli
– ravioli, Commented Oct 8, 2019 at 5:46

Gordon Linoff · Accepted Answer · 2019-10-08 11:44:11Z

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.

gotqn · Accepted Answer · 2019-10-08 05:47:06Z

1

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;

answered Oct 8, 2019 at 5:47

gotqn

44k47 gold badges167 silver badges257 bronze badges

Comments

man shitan2 · Accepted Answer · 2019-10-08 05:03:13Z

-1

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

answered Oct 8, 2019 at 5:03

man shitan2

462 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related