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

Question

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.

Don't turn off ONLY_FULL_GROUP_BY even if you do have privilege to do so. It's enabled for a good reason, and disabling it risks queries returning invalid or arbitrary results. — Bill Karwin, Apr 04 '22 at 17:15
Does this answer your question? [Error related to only\_full\_group\_by when executing a query in MySql](https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql) — philipxy, Apr 05 '22 at 06:39

score 2 · Accepted Answer · answered Apr 04 '22 at 17:13

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'.

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

1 Answers1