SQL aggregated subquery - Athena-CodePudding

Using AWS Athena I want to get total recovered per day by getting total recovered amount / total advances

here is code:

SELECT a.advance_date
    ,sum(a.advance_amount) as "advance_amount"
    ,sum(a.advance_fee) as "advance_fee"
    ,(SELECT 
        sum(credit_recovered fee_recovered) / (a.advance_amount a.advance_fee)
        FROM ncmxmy.ageing_recovery_raw_parquet 
        WHERE advance_date = a.advance_date 
        AND date(recovery_date) <= DATE_ADD('day', 0, a.advance_date) 
) as "day_0"
FROM ageing_summary_advance_parquet a
GROUP BY a.advance_date
ORDER BY a.advance_date

I am getting an error

"("sum"((credit_recovered fee_recovered)) / (a.advance_amount a.advance_fee))' must be an aggregate expression or appear in GROUP BY clause"

CodePudding user response：

Your division gives the error because the denominator tries to use individual columns from the ageing_summary_advance_parquet table. In my perception of the query, you need to divide by the grouped sum of advance_amount and advance_fee columns. In that case, we can merge two grouped sets of data by advance_date into the division. Please let me know if this query helps:

WITH cte1 (sum_adv_date, advance_date) as
(SELECT 
    sum(credit_recovered fee_recovered) as sum_adv_date, advance_date 
    FROM ncmxmy.ageing_recovery_raw_parquet 
    WHERE date(recovery_date) <= DATE_ADD('day', 0, advance_date)
    GROUP BY advance_date
),
cte2 (advance_date, advance_amount, advance_fee) as 
(SELECT
    a.advance_date
    ,sum(a.advance_amount) as "advance_amount"
    ,sum(a.advance_fee) as "advance_fee"
    FROM ageing_summary_advance_parquet a
    GROUP BY a.advance_date
)
SELECT cte2.advance_amount, cte2.advance_fee,
(cte1.sum_adv_date/(cte2.advance_amount cte2.advance_fee)) as "day_0"
FROM cte1 inner join cte2 on cte1.advance_date = cte2.advance_date
ORDER BY cte1.advance_date