Using Median aggregate in same query as avg?

user_13242894 0
Viewed 1659

Is there a way around this error?

Using LISTAGG/PERCENTILE_CONT/MEDIAN aggregate functions with other distinct aggregate function not supported

My code:

select
    date, 
    count(distinct listing_id) as count_listings, 
    count(distinct inquiry_id) as count_inquiries,
    avg(days_btw_start_date_first_inquiry) as avg_days_btw_start_date_first_inquiry, 
    median(days_btw_start_date_first_inquiry) as med_days_btw_start_date_first_inquiry
from together 
group by 1

The median is throwing the error, but I'm hoping to have the counts + avg + median in one table.

Thanks!

amazon-redshift
1 Answers

as a workaround you can join 2 grouped results

select
    t.date,
    avg(days_btw_start_date_first_inquiry) as avg_days_btw_start_date_first_inquiry,
    median(days_btw_start_date_first_inquiry) as med_days_btw_start_date_first_inquiry,
    t2.count_listings,
    t2.count_inquiries
from together t
    left join (
        select date,
            count(distinct listing_id) as count_listings,
            count(distinct inquiry_id) as count_inquiries
        from together
    ) t2 on t.date = t2.date
group by 1

It should not make a very significant impact on performance if your data volume is not counting in TB. For a table of 50GB and very similar query such workaround makes +20% run time impact against query without join and distinct functions.

user_7983350
Related