5

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!

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Stanley
  • 87
  • 1
  • 8

1 Answers1

7

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
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
  • Thanks for the hint. I'm trying to get the form like `7.430000` . I use the following code and finally got it. `Select ROUND(cast(Avg(Qtyshipped+0.00) as decimal(8,6)),2) From TblShipLine WHERE MethodShipped= 'FEDEX'` thank you so much! – Stanley Mar 11 '17 at 22:22
  • 1
    @Chao avg(int) will return an int... so to circumvent that, we just add 0.0 to the value which is an implicit conversion to a float – John Cappelletti Mar 11 '17 at 22:31