CODEWITHSUNDEEP
t-sqlsql-server-2014
MISNole
MISNole

Reputation: 1062

Show Only 2 Decimal Places in SQL Server

I've got some columns that are stored as an INT value that I am doing some addition and division on and I would like to display the results by limiting the digits after the decimal to 2. I've tried different combinations of DECIMAL / NUMERIC / ROUND but I can't get the solution.

Could anyone offer any advice on how to get the desired solution?

Query:

SELECT cc.code AS [CountyID], RTRIM(LTRIM(cc.[description])) AS [CountyName],
SUM(ISNULL(ps.push_count,0)) AS [CountyPushCounts],
SUM(ISNULL(ps.push_unique_count,0)) AS [UniquePushCount],
SUM(ISNULL(ps.error_count,0)) AS [PushErrorCount],
SUM(ISNULL(ps.warning_count,0)) AS [PushWarningCount],

(CAST(SUM(ISNULL(ps.push_unique_count,0)) AS DECIMAL(15,2)) / CAST(SUM(ISNULL(ps.push_count,0)) AS DECIMAL(15,2)) * 100.0) AS [Unique Push Per Day] ,

((CAST(SUM(ISNULL(ps.error_count,0)) AS DECIMAL(15,2)) + CAST(SUM(ISNULL(ps.warning_count,0)) AS DECIMAL(15,2))) / CAST(SUM(ISNULL(ps.push_count,0)) AS DECIMAL(15,2)) * 100.0) AS [Data Error Rate]

FROM dbo.push_stats AS [ps]
INNER JOIN CCIS.dbo.county_codes AS [cc] ON ps.county_code = cc.code
WHERE DATEPART(YEAR,ps.ldstat_date) = 2017
AND DATEPART(MONTH,ps.ldstat_date) = 3
GROUP BY cc.code, cc.[description]
ORDER BY cc.[description];

And my data set looks as follows:

CountyID  CountyName  PushCounts  UniquePushCount  PushErrorCount  PushWarningCount  [Unique Push Per Day]  [Data Error Rate]
1         ALACHUA     210422      77046            0               39                36.61499273            0.018534184
2         BAKER       8099        5306             0               71                65.51426102            0.876651438
3         BAY         3178214     434893           117             2793              13.68356568            0.091560857
4         BRADFORD    17654       12119            0               131               68.64733205            0.742041464

Upvotes: 0

Views: 8147

Answers (2)

Joel Coehoorn
Joel Coehoorn

Reputation: 416131

I think this will do it:

SELECT cc.code AS [CountyID], RTRIM(LTRIM(cc.[description])) AS [CountyName],
    SUM(ISNULL(ps.push_count,0)) AS [CountyPushCounts],
    SUM(ISNULL(ps.push_unique_count,0)) AS [UniquePushCount],
    SUM(ISNULL(ps.error_count,0)) AS [PushErrorCount],
    SUM(ISNULL(ps.warning_count,0)) AS [PushWarningCount],

    CAST((SUM(ISNULL(ps.push_unique_count,0)) * 100.00) / SUM(ISNULL(ps.push_count,0)) AS DECIMAL(15,2)) AS [Unique Push Per Day] ,   
    CAST((SUM(ISNULL(ps.error_count,0)) + SUM(ISNULL(ps.warning_count,0)) * 100.00) / SUM(ISNULL(ps.push_count,0)) AS DECIMAL(15,2)) AS [Data Error Rate]

FROM dbo.push_stats AS [ps]
INNER JOIN CCIS.dbo.county_codes AS [cc] ON ps.county_code = cc.code
WHERE DATEPART(YEAR,ps.ldstat_date) = 2017
    AND DATEPART(MONTH,ps.ldstat_date) = 3
GROUP BY cc.code, cc.[description]
ORDER BY cc.[description];

There are two main points here:

  1. With the division operation, get at least one term to a floating point type of some kind before the division happens, so that it doesn't do integer division and truncate the decimal portion of the result. You're okay as long as either term is a floating point type of some kind, and you can accomplish this simply by moving the * 100.00 earlier.
  2. You want to allow much greater than 2 decimal places for the internal calculations, and only set your output format at the very end. Rounding or casting to limited types too soon in an expression can change intermediate values in small ways that are exaggerated in the final results. This means you only want one big CAST() operation around the whole set.

Upvotes: 1

Adam Jacobson
Adam Jacobson

Reputation: 564

You have to cast the final result, not just the individual numerator and denominator

DECLARE @Numerator INTEGER
DECLARE @Denominator INTEGER

SET @Numerator = 10
SET @Denominator = 3;
-- This will produce 3.33333333
SELECT CAST(@Numerator AS DECIMAL(5,2))/CAST(@Denominator AS DECIMAL(5,2)) 
-- This will give you 3.33
SELECT CAST(
     CAST(@Numerator AS DECIMAL(5,2))
          /CAST(@Denominator AS DECIMAL(5,2)) 
       AS DECIMAL(5,2))

Upvotes: 1

Related Questions