CODEWITHSUNDEEP
sqlselectcaseaverage
Processit
Processit

Reputation: 55

Decimal place in select statement sql

I have a perfectly working select statement which I have been using. However I have noticed that it does not return any more than zero decimal places. Which I thought Excel had been factoring out, when copied across, but its not.

I have tried to change the format, but I still get zero decimal places. I have reviewed all posts on here, but as I am using a Case statement as well it is not simple to include.

Round(AVG(case when [Duration] > 0 then [Duration] end), 3) as AVGLOS,

Any help welcomed as always.

Upvotes: 0

Views: 320

Answers (1)

sepupic
sepupic

Reputation: 8687

Data type is an int

That is your problem.

The avg of integer values is always integer:

   declare @t table (col int);
   insert into @t values (2), (3);

   select avg(col)
   from @t;
----
2

So you should manipulate decimals, not integers like this:

   declare @t table (col int);
   insert into @t values (2), (3);

   select avg(col * 1.)
   from @t;
---
2.500000

So in your case just use this:

Round(AVG(case when [Duration] > 0 then [Duration]* 1. end), 3) as AVGLOS

Upvotes: 1

Related Questions