Avergae Decimal

Question

Post reply

Avergae Decimal

james.ingamells

SSC Veteran

Points: 274
More actions
October 22, 2013 at 3:21 am

#284068

Hi,
I am having an issue with an averageg i am trying to calculate. Here is the query. The results i am getting back are 27.00, 12.00 etc, when they should be 27.38, 12.67 etc.
Any advice?
SELECT
Week,
Hospital,
CAST( avg(Length_Of_Stay) AS DECIMAL(10,2)) as Average_LoS
FROM table
GROUP BY
Week,
Hospital

Viewing 4 posts - 1 through 3 (of 3 total)

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

Sean Pearce SSCoach Points: 15999 More actions · Answer 1

Try casting before applying the average

SELECT

Week,

Hospital,

CAST( avg(Length_Of_Stay) AS DECIMAL(10,2)) as Average_LoS,

avg(CAST(Length_Of_Stay AS DECIMAL(10,2))) as Average_LoS_CAST

FROM table

GROUP BY

Week,

Hospital

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

ParulPrabu SSCrazy Points: 2271 More actions · Answer 2

james.ingamells (10/22/2013)
Hi,
SELECT
Week,
Hospital,
CAST( avg(Length_Of_Stay) AS DECIMAL(10,2)) as Average_LoS
FROM table
GROUP BY
Week,
Hospital

This "Length_Of_Stay" might be INTEGER in your table.

So that AVG function returns INTEGER values.

You should convert data to decimal then get AVG value.

mario17 SSCertifiable Points: 6079 More actions · Answer 3

You can also do you own AVG like =1.0*Total_Days/Total_Count, that 1.0 it will you give you bunch of decimals places,

AVG has its own defaults I think.

Mario