round to two digit after decimel

Question

round to two digit after decimel

QQ-485619

Ten Centuries

Points: 1191
More actions
October 26, 2014 at 9:21 am

#300504

I run the following statement: select 1/3 and I got 0.
How do I do so that I can get 0.33?
Thanks,
Jian

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 1

QQ-485619 (10/26/2014)
I run the following statement: select 1/3 and I got 0.
How do I do so that I can get 0.33?
Thanks,
Jian

SQL Server performs an Integer division on whole numbers, if you don't want integer division then add a decimal separator.

😎

SELECT 1./3.

Results

0.333333

QQ-485619 Ten Centuries Points: 1191 More actions · Answer 2

what if that is a column name, e.g.

select sum(saletotal)/NumberofDays

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 3

QQ-485619 (10/26/2014)
what if that is a column name, e.g.
select sum(saletotal)/NumberofDays

Either to an explicit type cast to numeric or float using cast or convert or multiply the column value with 1.0, here's a quick example

😎

USE tempdb;

GO

SET NOCOUNT ON;

;WITH SAMPLE_DATA(DIVIDEND,DIVISOR) AS

(

SELECT 1 , 3 UNION ALL

SELECT 2 , 5 UNION ALL

SELECT 3 , 7 UNION ALL

SELECT 4 , 11 UNION ALL

SELECT 5 , 13 UNION ALL

SELECT 6 , 17 UNION ALL

SELECT 7 , 23

)

SELECT

DIVIDEND

,DIVISOR

,DIVIDEND / DIVISOR AS INTEGER_DIVISION

,(DIVIDEND * 1.0) / DIVISOR AS NOT_INTEGER_DIVISION

FROM SAMPLE_DATA SD;

Results

DIVIDEND DIVISOR INTEGER_DIVISION NOT_INTEGER_DIVISION

----------- ----------- ---------------- ---------------------------------------

1 3 0 0.333333333333

2 5 0 0.400000000000

3 7 0 0.428571428571

4 11 0 0.363636363636

5 13 0 0.384615384615

6 17 0 0.352941176470

7 23 0 0.304347826086

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

QQ-485619 (10/26/2014)
what if that is a column name, e.g.
select sum(saletotal)/NumberofDays

select sum(saletotal)/NumberofDays+0.0

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 5

Jeff Moden (10/26/2014)
QQ-485619 (10/26/2014)
what if that is a column name, e.g.
select sum(saletotal)/NumberofDays
select sum(saletotal)/NumberofDays+0.0

Actually: select sum(saletotal)/(NumberofDays+0.0) -- << Remember order of precedence for mathematics.

QQ-485619 Ten Centuries Points: 1191 More actions · Answer 6

Thank you very much for all your help. It works beautifully...

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

Lynn Pettis (10/26/2014)
Jeff Moden (10/26/2014)
QQ-485619 (10/26/2014)
what if that is a column name, e.g.
select sum(saletotal)/NumberofDays
select sum(saletotal)/NumberofDays+0.0
Actually: select sum(saletotal)/(NumberofDays+0.0) -- << Remember order of precedence for mathematics.

COFFEE!!! I NEED COFFEE!!! 😀 Thanks for the catch, Lynn,

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 8

Jeff Moden (10/26/2014)
Lynn Pettis (10/26/2014)
Jeff Moden (10/26/2014)
QQ-485619 (10/26/2014)
what if that is a column name, e.g.
select sum(saletotal)/NumberofDays
select sum(saletotal)/NumberofDays+0.0
Actually: select sum(saletotal)/(NumberofDays+0.0) -- << Remember order of precedence for mathematics.
COFFEE!!! I NEED COFFEE!!! 😀 Thanks for the catch, Lynn,

Well, after seeing people getting the facebook posts of this wrong, it just jumps out at me now:

? = 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 X 0 + 1

DonlSimpson SSCertifiable Points: 7043 More actions · Answer 9

select left(cast(sum(saletotal) as money) / NumberofDays, CHARINDEX('.', cast(cast(sum(saletotal) as money) / NumberofDays as varchar(max)), 1) + 2)

Don Simpson

I'm not sure about Heisenberg.

MMartin1 One Orange Chip Points: 27655 More actions · Answer 10

select CAST(sum(saletotal) as money) /NumberofDays;

Should give you the correct significant digits.

----------------------------------------------------

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 11

DonlSimpson (10/28/2014)
select left(cast(sum(saletotal) as money) / NumberofDays, CHARINDEX('.', cast(cast(sum(saletotal) as money) / NumberofDays as varchar(max)), 1) + 2)

A lot of work to round a value to 2 decimal points. Try this:

select round(sum(saletotal) * 1.0 / NumberOfDays, 2);