MySql group by issue even with aggregated column in group by

Question

I'm trying to make this query to work

select bj.id,
       bj.m_id,       
       bj.b_s_id,
       bj.contact_phone,
       bj.f_a_date,
       bj.source_request_id,
       mw.w_id,
       COUNT(bjhs.id) as hs_count,
       CAST(IFNULL(SUM(bjhs.status), 0) as UNSIGNED) as hs_status
from b_j bj
inner join m_w mw on bj.m_id = mw.m_id
inner join b_j_h_s bjhs on bj.id = bjhs.b_j_id
inner join m_j mj on mj.m_id = bj.m_id
    and mj.j_id = 4
inner join m_w_j mwj on mwj.m_j_id = mj.id
    and mwj.m_w_id = mw.id
where bj.b_s_id = 2
    and (DATEDIFF(CURDATE(), (select min(bjhs.created_at)
        from b_j_h_s bjhs
        where bjhs.b_j_id = bj.id
        group by bjhs.b_j_id
        limit 1)) >= 35)
    and bj.deleted_at is null
having hs_count != hs_status
group by bj.id,
       bj.m_id,
       bj.contact_phone,
       bj.f_a_date,
       bj.source_request_id,
       mw.w_id

But I'm getting the syntax error. My problem is that I must group by (the last one, not the one inside the subquery) the aggregate columns due to sql_mode=only_full_group_by, but using some or all of the columns I get the same error. What could possibly be the problem and how can I fix it?

Here's a fiddle

PS. An important note is that I can't change the sql_mode=only_full_group_by and the MySql version is 8.0.31

Answer 1

I think there are a few issues:

• having hs_count != hs_status should be defined after group by
• mw.w_id should be mw.m_id
• column bj.bariatric_status_id doesn't exist in the shared schema

Fiddle