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

Answer 2

In order to apply aggregate functions like min, SQL requires you to be very specific about what set of data that aggregate applies to. Even if SQL allowed the query you've written, you'd still only get the minimum created_date for each row, not each tenant_id.

In order to do what I think you're trying to do, you should use a sub-query to get the minimum created_date for each tenant_id, then use that value inform your active field.

SELECT o.tenant_id AS tenant_id,
       CASE WHEN o.created_date >= min_created_date THEN TRUE ELSE FALSE END AS active
FROM   reporting.t_order o
       LEFT JOIN
       (SELECT tenant_id, MIN (created_date) AS min_created_date
        FROM   reporting.t_me_bill_pay_charge
        WHERE  retired_date IS NULL) mbpc
           ON o.tenant_id = mbpc.tenant_id
WHERE  o.retired_date IS NULL

Generally, if you find yourself trying to cheat the SQL syntax requirements by doing something like group by 1, that's a strong indication that your approach is flawed.