SQL - If statement "this is incompatible with sql_mode=only_full_group

I have the following SQL

SELECT   a.id  ,
         a.name,
         COUNT( se.end_date )
FROM     accounts a
         INNER JOIN items it
         ON       it.account_id = a.id
         INNER JOIN schedule_elements se
         ON       it.id = se.item_id
WHERE    se.end_date   >= '2022.04.04'
GROUP BY a.id,
         a.name;

This returns only the rows (275) that pass the where clause. I need to return all the accounts (2297) but I only want to show the count if the end_date is today or greater.

I tried this:

SELECT   a.id  ,
         a.name,
         IF( se.end_date >= '2022.04.04', COUNT( se.end_date ), ' 0 ')
FROM     accounts a
         INNER JOIN items it
         ON       it.account_id = a.id
         INNER JOIN schedule_elements se
         ON       it.id = se.item_id
GROUP BY a.id  ,
         a.name;

as I thought it would put the count in if it was greater than the 4th or 0 if it wasn't. But when I run it I get the following.

#42000Expression #3 of SELECT list is not in GROUP BY clause and 
contains nonaggregated column 'se.end_date' which is not functionally dependent 
on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Googling the error it seems like I can turn off this but I can't make any changes to the DB so I think that's out of the question.

CodePudding user response：

You should enclose the condition inside COUNT():

COUNT(CASE WHEN se.end_date >= '2022-04-04' THEN 1 END)

or, with SUM():

SUM(se.end_date >= '2022-04-04')

or:

COALESCE(SUM(se.end_date >= '2022-04-04'), 0)

just in case there are only nulls in end_date.

If you actually want always the current date then use CURRENT_DATE instead of '2022-04-04'.