A Database Error Occurred Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column

2
votes

I have this query

SELECT substring(TGLLAHIR, 1, 7) as TGLLAHIR_,
       substring(TGLLAHIR, 5, 2) as BULAN, `TGLLAHIR` as `TGL`
FROM `m_pasien` 
WHERE substring(TGLLAHIR,1,4) = '2013' 
GROUP BY substring(TGLLAHIR, 1, 7) 
ORDER BY `TGLLAHIR` ASC

but message error showing

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'rsukemba_kojarsuk.m_pasien.TGLLAHIR' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

How do I solve this?

4
mysqlsqldatabase
What part of the error message do you not understand? It seems very clear to me. – Gordon Linoff
Why are you even using a GROUP BY in the first place? You aren't using any aggregates in your query. GROUP BY makes no sense in your query. – Siyual
can you tell me why? TGLLAHIR is a date i just want to take year – user7474611
SQL is not easy to gasp. – David דודו Markovitz

4 Answers

0
votes

You don't need group by, use distinct instead

SELECT  distinct 
        substring(TGLLAHIR, 1, 7) as TGLLAHIR_
       ,substring(TGLLAHIR, 5, 2) as BULAN
       ,`TGLLAHIR` as `TGL` 
FROM `m_pasien` 
WHERE substring(TGLLAHIR,1,4) = '2013' 
ORDER BY `TGLLAHIR` ASC
0
votes

It seems that you are misusing the group by clause in this query. Group by is useful when aggregating data. For example, if you had a table of accounts, dates, and deposit amounts; and you wanted to know the total deposits into each account over all dates, you could write something like

select account, sum(deposits)
    from some_table
    group by account

It's unclear from you question alone what exactly you're trying to do, and even harder because your strangely-named variables are listed without any context. BUT! it's probably safe to say that you don't need to use a group by here, so just remove it.

P.S. I would be wary of doing a select distinct unless you know what you're throwing away and are certain that you don't need it.

0
votes

You should use ANY_VALUE() aggregate function to suppress this warning message and make the query runnable. MySQL doesn't know which value of the non-aggregated field it should include in the result set. Every field that is not part of the GROUP BY clause should be wrapped in the aggregate function Here is, how it should look like:

SELECT substring(TGLLAHIR, 1, 7) as TGLLAHIR_,
       substring(TGLLAHIR, 5, 2) as BULAN, 
ANY_VALUE(`TGLLAHIR`) as `TGL`
FROM `m_pasien` 
WHERE substring(TGLLAHIR,1,4) = '2013' 
GROUP BY substring(TGLLAHIR, 1, 7) 
ORDER BY `TGLLAHIR` ASC

And here is the explanation of this point in MySQL refference: https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_any-value

0
votes

I think this problem due to strict mode enabled in your MySQL version. Kindly disable strict mode and try again.

To check whether strict mode is enabled or not run the below sql:

SHOW VARIABLES LIKE 'sql_mode';

If one of the value is STRICT_TRANS_TABLES, then strict mode is enabled.

To disable strict mode run the below sql:

set global sql_mode='';

Try again..