SQL Server Update not updating the decimals

Question

I am trying to update a column with DECIMAL(5,2) datatype. However, SQL Server seems to be rounding it and not storing the decimal places.

For example, one of the values is 1.53 and its getting stored as 1.00 in the table.

Here's the query

DECLARE @MaxLike DECIMAL (5,2), @MaxComment DECIMAL (5,2), @MaxFavourite DECIMAL (5,2)
SET @MaxLike = 1.0*100/(SELECT MAX(z.MaxLike) FROM (SELECT(COUNT(COALESCE(Liked,0))) AS MaxLike FROM Design_LikeRating GROUP BY DesignID) z)
SET @MaxComment = 1.0*100/(SELECT MAX(z.MaxComment) FROM (SELECT(COUNT(COALESCE(CommentID,0))) AS MaxComment FROM Comment_CommentDetail WHERE IsDeleted = 0 GROUP BY OverallParentGUID) z)
SET @MaxFavourite = 1.0*100/(SELECT MAX(z.MaxFavourite) FROM (SELECT (COUNT(COALESCE(DesignID,0))) AS MaxFavourite FROM Design_Favourite GROUP BY DesignID) z)


-- VoteCount and Rate
DECLARE @table1 TABLE (DesignID INT PRIMARY KEY, Rate DECIMAL(5,2))
INSERT INTO @table1
SELECT DesignID, CAST (Rate AS DECIMAL(5,2)) AS Rate
FROM 
(
SELECT  DesignID, COUNT(COALESCE(Liked,0.0)) AS Rate
FROM Design_LikeRating WITH (NOLOCK)
GROUP BY DesignID
) z

-- FavCount
DECLARE @table2 TABLE (DesignID INT PRIMARY KEY, FavCount DECIMAL(5,2))
INSERT INTO @table2
SELECT DesignID, CAST (FavCount AS DECIMAL (5,2)) AS FavCount
FROM
(
SELECT DesignID, COUNT(COALESCE(DesignID,0.0)) AS FavCount 
FROM Design_Favourite WITH (NOLOCK)
GROUP BY DesignID
) x

-- CommentCount
DECLARE @table3 TABLE (DesignGUID UNIQUEIDENTIFIER PRIMARY KEY, CommentCount DECIMAL(5,2))
INSERT INTO @table3
SELECT OverallParentGUID, CAST (CommentCount AS DECIMAL(5,2)) AS CommentCount
FROM
(
SELECT OverallParentGUID, COUNT(DISTINCT COALESCE(x.DesignID,0.0)) AS CommentCount 
FROM Comment_CommentDetail z WITH (NOLOCK) 
    INNER JOIN Design_DesignDetail x WITH (NOLOCK) ON x.DesignGUID = z.OverallParentGUID
WHERE z.IsDeleted = 0 AND x.UserIDInt != z.UserIDInt
GROUP BY OverallParentGUID
) y
;

WITH CTE AS
            (
            SELECT (COALESCE((1.0 * z.Rate*@MaxLike),0.0) + COALESCE((1.0 * y.FavCount*@MaxFavourite),0.0) + COALESCE((1.0 * x.CommentCount*@MaxFavourite),0.0)) AS PopularityScore, a.DesignID
            FROM Design_DesignDetail a 
                LEFT JOIN @table1 AS z ON z.DesignID = a.DesignID 
                LEFT JOIN @table2 AS y ON y.DesignID = a.DesignID 
                LEFT JOIN @table3 AS x ON x.DesignGUID = a.DesignGUID 
            WHERE DesignID > 300000 and DesignID = 444409
            )

UPDATE  Design_DesignDetail
SET     PopularityScore =  z.PopularityScore
FROM    Design_DesignDetail a 
        INNER JOIN CTE z  on z.DesignID = a.DesignID

UPDATE I'll stop trying to be clever and post the whole query here. As you can see, its got a little bit of everyone's answer but it still isnt working!

score 4 · Accepted Answer · edited May 23 '17 at 12:19

4

This expression must give an int or a decimal with scale 0 (no dec places)

(ISNULL((z.Rate*@MaxLike),0) + ISNULL((y.FavCount*@MaxFavourite),0) + ISNULL((x.CommentCount*@MaxFavourite),0))

I'm guessing you'd need to CAST to float inside each ISNULL without knowing the datatypes

And please don't use NOLOCK like that

Edit, after full code update

Your COUNT aggregates will return int values so your decimal(5,2) values probably have xxx.00 in them.

Some comments added to @table1 load

INSERT INTO @table1
SELECT DesignID,
-- gbn The CAST changes 2 to 2.00
CAST (Rate AS DECIMAL(5,2)) AS Rate
FROM 
(
SELECT  DesignID,
     -- gbn COALESCE does nothing. COUNT gives int and simply counts non-null values
     COUNT(COALESCE(Liked,0.0)) AS Rate 
FROM Design_LikeRating WITH (NOLOCK)
GROUP BY DesignID
) z

See my answer here Count(*) vs Count(1) for more explanation

edited May 23 '17 at 12:19

Community

1
1

answered Nov 12 '10 at 20:28

gbn

422,506
82
585
676

I so knew someone was going to say that! Anyway, when I do a select on it, it shows the 2 decimal places? – super9 Nov 12 '10 at 20:30
@Nai you can also try to put a 0.0 at the right of the isnull like ISNULL((z.Rate*@MaxLike),0.0) and see what happens. – Jose Chama Nov 12 '10 at 20:33
@Nai: SELECT on the CTE bit? If this gives 1.53 but a SELECT on DesignDetail gives 1.00 then do you have a trigger? There *is* a datatype mismatch somewhere and integer math – gbn Nov 12 '10 at 20:35
@Jose Chama: ISNULL takes datatype of left hand expression. You'd need COALESCE for your idea to work (which uses the highest precedence datatype) – gbn Nov 12 '10 at 20:36

score 1 · Answer 2 · answered Nov 12 '10 at 20:28

1

You are probablly suffering somewhere from integer math.

And never use float when you are doing a calulation unless you like rounding errors.

answered Nov 12 '10 at 20:28

HLGEM

94,695
15
113
186

score 1 · Answer 3 · answered Nov 12 '10 at 20:33

1

What are the types of z.Rate, @MaxLike, y.FavCount, @MaxFavourite, and x.CommentCount?

I bet y.FavCount and x.CommentCount are ints.

answered Nov 12 '10 at 20:33

Amy B

108,202
21
135
185

You're right, they were INTS but I've since swapped them to DECIMAL(5,2) – super9 Nov 12 '10 at 20:52

Stu · Answer 4 · 2010-11-12T20:41:03.590

0

SELECT ISNULL(1.0 * z.Rate*@MaxLike, 0.0) +
       ISNULL(1.0 * y.FavCount*@MaxFavourite, 0.0) + 
       ISNULL(1.0 * x.CommentCount*@MaxFavourite, 0.0) AS PopularityScore, a.DesignID

edited Nov 12 '10 at 20:41

answered Nov 12 '10 at 20:35

Stu

15,675
4
43
74

It would be applied too late. The damage (int maths) is done at "z.Rate*@MaxLike" – gbn Nov 12 '10 at 20:37
-1 your edit still won't help. (z.Rate*@MaxLike) etc will be evaluated first: they're in brackets – gbn Nov 12 '10 at 20:44
@Stu: you edited *again*.. so my comments were valid at the time I posted. -1 removed anyway. – gbn Nov 12 '10 at 20:47
Ah! Yes, I submitted prematurely -- didn't think anyone got in there in the 5 seconds. Sorry if I sounded huffy. – Stu Nov 12 '10 at 20:50

SQL Server Update not updating the decimals

4 Answers4