# Thread: CAST or CONVERT When Calc'ing an AVG from INTs?

1. Registered User
Join Date
Oct 2010
Location
Atlanta, GA
Posts
211

## Unanswered: CAST or CONVERT When Calc'ing an AVG from INTs?

Just a general question here. I have an INT field that I want to take the average value of, to the 2nd decimal. Is there a difference between using CAST vs CONVERT? Maybe I am over complicating this...

CAST
Code:
```CAST(Avg(CAST(units AS DECIMAL(10,2))) AS DECIMAL(10,2))
from myTable```
CONVERT
Code:
```CONVERT(numeric(10,2),avg(CONVERT(numeric(10,2),units)))
from myTable```

2. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
There's not much different between Cast and Convert. For your current purpose they are identical.

The Cast() function is the ANSI standard function.
The proprietary Convert() function has a 3rd optional parameter you can pass to it to "format" certain data types (e.g. dates).

Incidentally, you don't need to perform the action twice:
Code:
```SELECT Avg(Cast(units As decimal(10,2)) As casted
, Avg(Convert(decimal(10,2), units)) As converted
FROM   your_table```

3. Registered User
Join Date
Oct 2010
Location
Atlanta, GA
Posts
211
Originally Posted by gvee
There's not much different between Cast and Convert. For your current purpose they are identical.

The Cast() function is the ANSI standard function.
The proprietary Convert() function has a 3rd optional parameter you can pass to it to "format" certain data types (e.g. dates).

Incidentally, you don't need to perform the action twice:
Code:
```SELECT Avg(Cast(units As decimal(10,2)) As casted
, Avg(Convert(decimal(10,2), units)) As converted
FROM   your_table```
Thanks for the info, but if i don't CAST/CONVERT twice, then the results are not rounded to 2 decimals. The above formulas both output 3.390089 instead of the desired 3.39.

4. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
It is REALLY hard to compute averages at a specific level of precision using Transact-SQL because the engine itself coerces DECIMAL data before the computation.
Code:
```DECLARE @v1 sql_variant
,  @v2 sql_variant

--  Cast Integers to DECIMAL as a test

SELECT @v1 = Avg(Cast(id AS Decimal(20, 2)))
,  @v2 = Avg(Convert(DECIMAL(20, 2), id))
FROM sysobjects

--  This confirms http://technet.microsoft.com/en-us/library/ms187810.aspx

SELECT '@v1' AS varname, @v1 AS value
,  SQL_Variant_Property(@v1, 'BaseType') AS Data_Type
,  SQL_Variant_Property(@v1, 'Precision') AS Precision
,  SQL_Variant_Property(@v1, 'Scale') AS scale
UNION SELECT '@v2' AS varname, @v2 AS value
,  SQL_Variant_Property(@v2, 'BaseType') AS Data_Type
,  SQL_Variant_Property(@v2, 'Precision') AS Precision
,  SQL_Variant_Property(@v2, 'Scale') AS scale```
-PatP

5. Registered User
Join Date
Jan 2013
Posts
355