SQLTeam.com | Weblogs | Forums

Decimal point is not showing


#1

SELECT FB.TEACHER_ID AQ,QUES_CATEGORY, CAST(AVG(RATING) AS decimal(5,2)) AS AVG_RATING FROM ADD_QUESTION AQ
JOIN FEEDBACK FB
ON AQ.DESCRIPTION = FB.DESCRIPTION
WHERE TEACHER_ID = 'LEC03'
GROUP BY FB.TEACHER_ID,AQ.QUES_CATEGORY


SELECT FB.TEACHER_ID AQ,QUES_CATEGORY, CAST(AVG(RATING) AS decimal(5,2)) AS AVG_RATING FROM ADD_QUESTION AQ
JOIN FEEDBACK FB
ON AQ.DESCRIPTION = FB.DESCRIPTION
WHERE TEACHER_ID = 'LEC03'
GROUP BY FB.TEACHER_ID,AQ.QUES_CATEGORY

in the second sql code i want to show the avg of rating of each category by group
subject command,
time sense
but i was unable to get the decimal information , i.e it average rating is rounded.

expected output is

your response and help is highly appreciable .
Tanks in advance.


#2

You seem to be doing the AVG with integers and then converting to decimal. It should probably be the other way round.

AVG(CAST(RATING  AS decimal(5,2)))

#3

IFOR,

Its not working


#4

What error message or incorrect result?

"Its not working" provides no clue for anyone who wants to try to help you.


#5

Out put is 1.00000 and 2.00000 respectively even here the decimal is rounded


#6

What is your current code?


#7

Prove it.


#8

even if i use the AVG(CAST(RATING AS decimal(5,2)))

Out put is 1.00000 and 2.00000 respectively even here the decimal is rounded


#9

Umm...

At a minimum you should provide a test harness:

CREATE TABLE #t
(
    Rating int NOT NULL
);
INSERT INTO #t
VALUES (1),(1),(1),(3),(1),(3);
--select * from #t;

This is basic elementary math - the average of an integer is an integer.

SELECT
    AVG(Rating) AS AVG_integer_RATING 
    ,CAST(AVG(Rating) AS decimal(5,2)) AS AVG_integer_RATING_as_decimal 
    ,AVG(CAST(Rating  AS decimal(5,2))) AS AVG_decimal_RATING
    ,AVG(Rating * 1.0) AS AVG_float_RATING
    ,ROUND(AVG(Rating * 1.0), 2) AS AVG_rounded_float_RATING
FROM #t;