mysql - добавление медианы к этому запросу

Question

Один идентификатор продукта имеет много цен. Я пытаюсь найти цены, которые, вероятно, неверны, сравнивая каждую цену со средней ценой продукта. В настоящее время у меня есть минимум и максимум в выводе и я хотел бы добавить один столбец для медианной цены каждого продукта. Я пробовал avg(), но это возвращает: Неверный синтаксис ввода для типа числовой. Есть идеи?

select 
distinct dp.product_id,
pc.warehouse_id as retailer_id,
pc.code,
dp.product_name,
dp.product_brand_name,
dp.product_size,
dp.product_unit_count,
pd.product_name as retailer_name,
pd.brand_name as retailer_brand_name,
pd.size as retailer_size,
pd.price,
sp.max_cost,
sp.min_cost
from dim_product as dp
join product_codes as pc on pc.product_id = dp.product_id and pc.deleted_ind = 'N'
join suspect_products as sp on sp.product_id = dp.product_id
left join (
select 
    nvl(json_extract_path_text(rf."raw",'brand_name'),dei.brand_name) as brand_name,
    nvl(json_extract_path_text(rf."raw",'name'),dei.name) as product_name,
    nvl(json_extract_path_text(rf."raw",'size'), dei.size) as size,
    nvl(json_extract_path_text(rf."raw",'cost_price_per_unit'),dei.cost_price_per_unit::VARCHAR) as price,
    nvl(rf.code,dei.lookup_code) as code,
    nvl(rf.retailer_id,dei.warehouse_id) as retailer_id
from product_retailer_file_data as rf
left join (
    select
        d.brand_name,
        d.name,
        d.size,
        d.cost_price_per_unit,
        d.lookup_code,
        d.warehouse_id
    from data_entry_items as d
    join (
        select lookup_code, max(created_at) as max_date
        from data_entry_items
        where created_at > current_date-720
        group by 1) as m on m.lookup_code = d.lookup_code and m.max_date = d.created_at
) as dei on dei.lookup_code = rf.code and dei.warehouse_id = rf.retailer_id
) as pd on pd.code = pc.code and pd.retailer_id = pc.warehouse_id
order by 1;