﻿ How to Make SQL Query Result Show With 2 Decimals - ITCodar

# 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.codeWHERE DATEPART(YEAR,ps.ldstat_date) = 2017    AND DATEPART(MONTH,ps.ldstat_date) = 3GROUP 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

``SELECTCONVERT(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.