CODEWITHSUNDEEP
sqlsql-serversql-server-2016
Stanley
Stanley

Reputation: 87

How to convert AVG() function to decimal in SQL SERVER

Help! I have a table looks like the following one. 

SELECT *
FROM tblshipline 
WHERE MethodShipped = 'FEDEX'

Then I get

Qtyshipped
2
3
15
3
10
9
10

Now the question is to calculate the AVERAGE of qtyshipped and round it to 2 decimal number.

My Code is 

SELECT CONVERT(decimal(8,6), ROUND(AVG(Qtyshipped),2))  
FROM TblShipLine
WHERE MethodShipped= 'FEDEX'

The Answer should be 7.430000 , But it always returns 7.000000. I appreciate any advice, thanks!

Upvotes: 5

Views: 9431

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81990

Here we take your INT value and add 0.0 which is a fast conversion to float. Then we apply the round() and convert()

Declare @YourTable table (Qtyshipped int)
Insert into @YourTable values
(2),
(3),
(15),
(3),
(10),
(9),
(10)

Select convert(decimal(8,6),round(Avg(Qtyshipped+0.0),2))
 From  @YourTable

Returns

7.430000

Upvotes: 7

Related Questions