The SELECT list is not in the GROUP BY clause and contains a non-aggregated column .... incompatible with sql_mode = only_full_group_by

Question

AM using MySQL 5.7.13 on my Windows WAMP Server PC

Here is my problem. By executing this query

SELECT *
FROM `tbl_customer_pod_uploads`
WHERE `load_id` = '78' AND
      `status` = 'Active'
GROUP BY `proof_type`

It always turns out an error like this

The expression # 1 in the SELECT list is not in the GROUP BY clause and contains the non-aggregated column "returntr_prod.tbl_customer_pod_uploads.id", which does not depend on the functionality in the columns in the GROUP BY clause; this is incompatible with sql_mode = only_full_group_by

Could you tell me the best solution ...

I need Result like

+----+---------+---------+---------+----------+-----------+------------+---------------+--------------+------------+--------+---------------------+---------------------+
| id | user_id | load_id | bill_id | latitude | langitude | proof_type | document_type | file_name    | is_private | status | createdon           | updatedon           |
+----+---------+---------+---------+----------+-----------+------------+---------------+--------------+------------+--------+---------------------+---------------------+
|  1 |       1 | 78      | 1       | 21.1212  | 21.5454   |          1 |             1 | id_Card.docx |          0 | Active | 2017-01-27 11:30:11 | 2017-01-27 11:30:14 |
+----+---------+---------+---------+----------+-----------+------------+---------------+--------------+------------+--------+---------------------+---------------------+

Answer 1

it

№1 SELECT GROUP BY 'returntr_prod.tbl_customer_pod_uploads.id', GROUP BY; sql_mode = only_full_group_by

sql MySQL ,

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

.. , , sql only_full_group_by

... :-)

Answer 2

only_full_group_by MySQL only_full_group_by, , GROUP BY ANSI SQL. , , proof_type GROUP BY proof_type, :

• proof_type

"" , MIN(), MAX() AVG() . :

SELECT proof_type,
       MAX(id) AS max_id,
       MAX(some_col),
       MIN(some_other_col)
FROM tbl_customer_pod_uploads
WHERE load_id = '78' AND
      status = 'Active'
GROUP BY proof_type

MySQL GROUP BY SO, , , . , , .

: ANSI SQL , GROUP BY, , . . , . MySQL - , (SQL Server Oracle AFAIK).

Answer 3

sql_mode = only_full_group_by - MySql IDE

mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

Answer 4

(1) PHPMyAdmin

phpMyAdmin, " sql_mode ", .

"sql mode" "ONLY_FULL_GROUP_BY"

(2) .

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

(3) SELECT *

SELECT. , "group by", (MAX, MIN, SUM, COUNT ..)

---

, (1) (2), , .

(,/etc/mysql/my.cnf [mysqld]), MySQL:

: /etc/mysql/my.cnf

: sql_mode sql-mode

Answer 5

SQL92, GROUP BY.

SQL99 T301, GROUP BY: name custid, . , , .

MySQL 5.7.5 . SQL ONLY_FULL_GROUP_BY ( ), MySQL , , HAVING ORDER BY , GROUP BY .,

MySQL :: MySQL 5.7 :: 12.19.3 MySQL GROUP BY

, sql :

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

... !!!

Answer 6

:

Ubuntu

: sudo vi /etc/mysql/my.cnf

A

:

[mysqld]
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

esc,

Type :wq to save and close vim.

sudo service mysql restart, MySQL.

Answer 7

, , , / . :

GROUP BY `proof_type`, `id`

proof_type, id. , . / .

Answer 8

Dhanu K Anil Gupta , mysql , .

Answer 9

1. phpMyAdmin
2. : : localhost: 3306 .
4. "sql mode" : NO_ENGINE_SUBSTITUTION

.

Ec2, .

Answer 10

WAMP MySQL. "sql_mode", , "", , , sql_mode = "" .

MySQL, ...

.

Answer 11

my.ini :

[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

. :

[mysqld]
sql_mode = ""

: ONLY_FULL_GROUP_BY