CODEWITHSUNDEEP
sqlsql-serversql-server-2008
MrM
MrM

Reputation: 21999

How to get an average to 3 decimal places

I have the following code. The 3 decimal places are being displayed but the average is wrong. Is there a way I can restructure the code to achieve the correct result?

with total_indi_days as
(
    SELECT COUNT(*) AS total, DATENAME(DW, DateLog) AS NameOfDay 
    FROM  Access 
    GROUP BY DATENAME(DW, DateLog) 
    --ORDER BY total DESC 
)
,total_overall_days as
(
    SELECT COUNT(*) as total_days FROM  Access
)
select str(((total * 100)/ total_days ), 7, 3) as average, total, total_days, NameOfDay 
from total_indi_days, total_overall_days

result...

|average | total | total_days | NameOfDay
| 2.000  |  29   |   1000     | Sun           
| 18.000 | 188   |   1000     | Mon             
| 15.000 | 159   |   1000     | Tues         
| 20.000 | 207   |   1000     | Wed             
| 19.000 | 194   |   1000     | Thur           
| 17.000 | 171   |   1000     | Fri             
| 5.000  |  52   |   1000     | Sat

should be...

|average | total | total_days | NameOfDay
| 2.900  |  29   |   1000     | Sun           
| 18.800 | 188   |   1000     | Mon             
| 15.900 | 159   |   1000     | Tues         
| 20.700 | 207   |   1000     | Wed             
| 19.400 | 194   |   1000     | Thur           
| 17.100 | 171   |   1000     | Fri             
| 5.200  |  52   |   1000     | Sat

I would also like to know if there is a easier way to get this result. The "with" is a bit much, considering I have the AVG built in function, which did not work for me maybe because I was doing the wrong thing.

Upvotes: 1

Views: 4358

Answers (5)

EricZ
EricZ

Reputation: 6205

Both total and total_days are integer, therefore you have wrong result.

This should works.

select CAST((total * 100.000)/ total_days AS NUMERIC(19,3)) as average, total, total_days, NameOfDay from total_indi_days, total_overall_days

Upvotes: 0

gbn
gbn

Reputation: 432421

Change

select str(((total * 100)/ total_days ), 7, 3) as average  to

to

select str(((total * 100.0)/ total_days ), 7, 3) as average

This way, you don't have integer division...

Upvotes: 2

Tom H
Tom H

Reputation: 47392

It looks like the problem is that you're converting AFTER the math has been done. So, SQL Server is doing the division on integers and giving an integer result which is THEN converted to a decimal. Try changing the 100 to 100.0 and that should correct the issue.

You could also CAST the columns to decimals, but as soon as one of the values is a decimal, it should convert the rest over to give the answer as a decimal and simply making the 100 a 100.0 is the easiest way to do that.

Upvotes: 0

Galled
Galled

Reputation: 4206

In MySql you could use FORMAT

SELECT FORMAT(12332.1,4) => '12,332.1000'

Upvotes: -1

Phil Murray
Phil Murray

Reputation: 6554

You can use this methodology but it will also round the value. CONVERT(Numeric(10,3), (Count(ID) * 100. / @Total)) as Percentage

Upvotes: 0

Related Questions