How to write safe SQL query on strict SQL Mode

Question

I write query like this:

SELECT name, class, period FROM subject 
WHERE organization IN (?,?,?,?,?,?,?,?) 
GROUP BY class, period

But, it causing error like this:

Error 1055: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'bsi.subject.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I found solving from this. But, I am not so sure it safe. I am database junior. Is it safe? There is another solution without changing the sql mode setting?

I also found this solution. If column name not necessary, just simply remove it. But, I consider to add it to reuseable code.

Why are you using `GROUP BY` without any aggregation functions? Maybe it should be `GROUP_CONCAT(name)` to get all the names? — Barmar, Oct 11 '21 at 15:29
Hi @Elbo, from my personal experience it is better to not have queries where the number of nonaggregated columns in SELECT and GROUP BY differ. Depending on the settings over your database, you could end up with different results, and it also would be more confusing for anyone else who is reading your SQL. — Kurt Kline, Oct 11 '21 at 15:34
I think you can use it like this : `SELECT name, class, period FROM subject WHERE (class, period) in ( select class,period from subject where organization IN (?,?,?,?,?,?,?,?) GROUP BY class, period) ;` — Ergest Basha, Oct 11 '21 at 15:44
Imagine that 2 different `name` values matches the same `(class, period)` value. Server must return one row. What `name` value from 2 possible ones it must to return? — Akina, Oct 11 '21 at 15:45

score 1 · Accepted Answer · answered Oct 11 '21 at 16:04

Every column in the Select statements has to be in the GROUP BY or have aN Aggregation function

like

SELECT GROUP_CONCAT( DISTINCT   name) As name, class, period 
FROM subject 
WHERE organization IN (?,?,?,?,?,?,?,?) 
GROUP BY class, period

this would add all names from one period and class as a comma seperated strntg

How to write safe SQL query on strict SQL Mode

1 Answers1