CODEWITHSUNDEEP
sqlamazon-web-servicesamazon-redshift
es1713
es1713

Reputation: 45

Redshift Avg not returning decimals

I can't figure out how to return a decimal in an average calculation.

I have:

select
    r_i_flag,
    mr_age_bucket,
    count(request_id) as sum_requests,
    avg(age_of_mr_days) as avg_age_of_mr_days,
    median(age_of_mr_days) as median_age_of_mr_days
from
    together
group by
    1,
    2
order by
    1 desc,
    2

I've tried casting the mr_age_bucket as numeric, tried casting the avg/median as numeric, as a decimal, using to_number, but I'm just getting rounded numbers:

r_i_flag mr_age_bucket sum_requests avg_age_of_mr_days median_age_of_mr_days
Retail <7 days 29 1 1
Retail >7 Days 107 46 30
Institutional <7 days 1 5 5
Institutional >7 Days 13 45 29

I feel like this is a super silly thing but I can't figure it out. How do I return a non-rounded number? Thanks.

Upvotes: 1

Views: 4524

Answers (1)

Pavel Slepiankou
Pavel Slepiankou

Reputation: 3585

The issue is a data type passed into AVG, just dividing it with 1.0 will do the trick. Casting to DOUBLE PRECISION or REAL will also work

select
    r_i_flag,
    mr_age_bucket,
    count(request_id) as sum_requests,
    avg(age_of_mr_days / 1.0) as avg_age_of_mr_days,
    avg(age_of_mr_days::double precision),
    avg(age_of_mr_days::real),
    median(age_of_mr_days) as median_age_of_mr_days

from
    together
group by
    1,
    2
order by
    1 desc,
    2

Upvotes: 5

Related Questions