Error with aggregate function in a CASE statement and GROUP BY clause

Question

I have the following CTE that I am using in a larger query and I receive two different error messages based on how I group.

I am on Redash and using Amazon Athena. I can group by tenant_id or I can group by tenant_id & my case statement that is named "active". Either way I will receive an error.

active_billpay AS
  (SELECT o.tenant_id as tenant_id, CASE WHEN o.created_date >= min(mbpc.created_date) 
     THEN true else false end as active
    FROM reporting.t_order o
    LEFT JOIN reporting.t_me_bill_pay_charge mbpc ON o.tenant_id = mbpc.tenant_id
      WHERE o.retired_date is null
        AND mbpc.retired_date is null
    GROUP by 1),

If I group by only tenant_id:

Error running query: SYNTAX_ERROR: line 13:32: '(CASE WHEN ("o"."created_date" >= "min"("mbpc"."created_date")) THEN true ELSE false END)' must be an aggregate expression or appear in GROUP BY clause

If I group by both tenant_id and active:

Error running query: SYNTAX_ERROR: line 13:32: GROUP BY clause cannot contain aggregations or window functions: ["min"("mbpc"."created_date")]

Thank you in advance.

Answer 1

I think you just want to aggregate by tenant_id and created_date:

 SELECT o.tenant_id as tenant_id,
        (CASE WHEN o.created_date >= MIN(mbpc.created_date) THEN true ELSE false
         END) as active
 FROM reporting.t_order o LEFT JOIN
      reporting.t_me_bill_pay_charge mbpc
      ON o.tenant_id = mbpc.tenant_id
 where o.retired_date is null
 and mbpc.retired_date is null
 group by o.tenant_id, o.created_date