How to Make SQL Query Result Show With 2 Decimals

How to display two digits after decimal point in SQL Server

select cast(your_float_column as decimal(10,2))
from your_table

decimal(10,2) means you can have a decimal number with a maximal total precision of 10 digits. 2 of them after the decimal point and 8 before.

The biggest possible number would be 99999999.99

Rounding off to two decimal places in SQL

You could cast your result as numeric(x,2). Where x <= 38.

select
    round(630/60.0,2),
    cast(round(630/60.0,2) as numeric(36,2))

Returns

10.500000    10.50

Show Only 2 Decimal Places in SQL Server

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.

Trying to round the results of a sql query to 2 decimals

you could use convert for take control over the format

SELECT
CONVERT(VARCHAR, YEAR(COALESCE(release_date, requested_date))) 
    + RIGHT('00' + CONVERT(VARCHAR, 
 MONTH(COALESCE(release_date, requested_date))),2) as yrmnth
,salesrep
,customer_name
,  Convert(decimal(12,2), 
     SUM(price_per_ea * COALESCE(open_release_qty, open_order_qty)))  as ext_price 

SQL get decimal with only 2 places with no round

You could accomplish this via the ROUND() function using the length and precision parameters to truncate your value instead of actually rounding it :

SELECT ROUND(3381.5786, 2, 1)

The second parameter of 2 indicates that the value will be rounded to two decimal places and the third precision parameter will indicate if actual rounding or truncation is performed (non-zero values will truncate instead of round).

Example

You can see an interactive example of this in action here.

---

Related Topics