CODEWITHSUNDEEP
sql-servert-sql
DannyFar94
DannyFar94

Reputation: 67

ROUND T-SQL of values 0.0

I am working on a Select statement where I have to show the average of genres but the result must show the 0 values as 0.0

My code is 

SELECT genreName AS 'Genre'
       , CAST(CASE WHEN
                   AVG(rateValue) IS NULL
                   THEN 0
               ELSE ROUND(FORMAT(AVG(rateValue),2),0.0)
               END AS FLOAT) AS 'Average Rating'
FROM [IMDB].[FilmGenre] flmgnr
     LEFT JOIN [IMDB].[FilmGenreAllocation] flmgnrall
     ON(flmgnrall.genreId = flmgnr.genreId)
     LEFT JOIN [IMDB].[Film] flm
     ON(flm.filmId = flmgnrall.filmId)
     LEFT JOIN [IMDB].[FilmReview] flmrvw
     ON(flmrvw.filmId = flmgnrall.filmId)
GROUP BY genreName;
GO

and the result must be 

+---------+----------------+
|  Genre  | Average Rating |
+---------+----------------+
| Action  | 0.00           |
| Comedy  | 4.67           |
| Crime   | 4.50           |
| Drama   | 4.50           |
| Family  | 0.00           |
| Mystery | 4.40           |
+---------+----------------+

Upvotes: 1

Views: 2278

Answers (3)

Deadsheep39
Deadsheep39

Reputation: 611

Instead of

THEN 0

try

THEN 0.0

With 0 you told to SQL Server - case is int data type.

Or better way from:

 CAST(CASE WHEN
        AVG(rateValue) IS NULL
        THEN 0
        ELSE ROUND(FORMAT(AVG(rateValue),2),0.0)
     END AS FLOAT)

Make:

1.0 * ROUND(ISNULL(AVG(rateValue), 0), 1) -- isnull assume data type from first item

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81970

Agree with Zohar Peled. This should really be in the presentation layer.

However, if you must, one option is to use the Format() function if 2012+, or just cast as decimal

...Format(IsNull(AVG(rateValue),0),'#0.00')

or 

...Cast(IsNull(AVG(rateValue),0) as decimal(18,2))

Upvotes: 1

ggradnig
ggradnig

Reputation: 14169

Don't cast your values to float, but rather to DECIMAL(18,2) like so:

CAST(CASE WHEN
               AVG(rateValue) IS NULL
               THEN 0
           ELSE ROUND(FORMAT(AVG(rateValue),2),0.0)
           END AS DECIMAL(18,2)) AS 'Average Rating'

Upvotes: 0

Related Questions