CODEWITHSUNDEEP
sqlsql-server
Limna
Limna

Reputation: 403

Select Query: To Show Average of Columns in Decimal

I want to get value in decimal while calculating averave of column values in select query. For that I have used below query. But the value comes as 3.0 instead of 3.6. Is there any solution for that? 

SELECT P.ANSW_ONE,P.ANSW_TWO,P.ANSW_THREE,P.ANSW_FOUR,P.ANSW_FIVE, 
CAST(((P.ANSW_ONE+P.ANSW_TWO+P.ANSW_THREE+P.ANSW_FOUR+P.ANSW_FIVE)/5) AS DECIMAL(10,1)) AS ANSW_AVG 
FROM FEEDBACK P

enter image description here

Upvotes: 0

Views: 37

Answers (2)

McNets
McNets

Reputation: 10807

CAST the whole SUM instead of the result and use 5.0 just to force it to be decimal instead of integer.

SELECT P.ANSW_ONE,
       P.ANSW_TWO,
       P.ANSW_THREE,
       P.ANSW_FOUR,
       P.ANSW_FIVE, 
       (CAST((P.ANSW_ONE+P.ANSW_TWO+P.ANSW_THREE+P.ANSW_FOUR+P.ANSW_FIVE) DECIMAL(10,1) /5.0)) AS ANSW_AVG 
FROM   FEEDBACK P

Upvotes: 1

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8043

Try This

SELECT 
  P.ANSW_ONE,
  P.ANSW_TWO,
  P.ANSW_THREE,
  P.ANSW_FOUR,
  P.ANSW_FIVE, 
  CAST(CAST(P.ANSW_ONE+P.ANSW_TWO+P.ANSW_THREE+P.ANSW_FOUR+P.ANSW_FIVE AS DECIMAL(10,1))/5 AS DECIMAL(10,1)) AS ANSW_AVG  
FROM FEEDBACK P

Cast the sum before dividing by 5 and then again cast the result

Upvotes: 0

Related Questions