• Register
0 votes
33 views

Problem :

Currently I am using the MySQL 5.7.13 on the windows PC with the WAMP Server

While executing below query I am facing following error

SELECT *
FROM `tbl_customer_pod_uploads`
WHERE `load_id` = '78' AND
      `status` = 'Active'
GROUP BY `proof_type`
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'returntr_prod.tbl_customer_pod_uploads.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

 

8 4 2
2,300 points

Please log in or register to answer this question.

1 Answer

0 votes

Solution :

I saw your Group By Query on this forum. I can help you in resolving the issue.

Your issue can be very easily solved by just changing your sql mode in the MySQL by below command:

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
After this command you need to restart the MySql Service.
This worked for me too.I had used this as my project was having many Group By Queries so I simply changed the sql mode to the only_full_group_by

Now the MySQL's only_full_group_by mode is turned on by you, it means that the strict ANSI SQL rules will now apply when you will try to use GROUP BY. With reference to your query,it means that if you try to GROUP BY of a proof_type column, then you can only select below two things:

First is the proof_type column, or

Second is the aggregates of any other column.

5 2 1
4,980 points

Related questions

0 votes
1 answer 35 views
35 views
Problem : I am currently using MySQL 5.7.13 on my windows PC with the WAMP Server I am facing following error while executing below the query. Error: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ' ... functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by Does anybody have any solution for above error?
asked Nov 29, 2019 alecxe 7.5k points
0 votes
1 answer 104 views
104 views
Problem : I want to select a maximum avg for each cname in my table. SELECT cname, wmname, MAX(avg)  FROM makerar GROUP BY cname; But I will get the below error, ERROR:  column "makerar.wmname" must appear in the GROUP BY clause or be used in an   aggregate ... )  FROM makerar GROUP BY cname; How can I go about fixing above error? Note: This table is the VIEW created from the previous operation.
asked Jan 22, 2020 jwilliam 3.9k points
–1 vote
1 answer 60 views
60 views
Problem : I want to display the column B in my below mentioned SQL query, but when I try to add it to my query it gives me following error: Column T2.M' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. My code: SELECT L, COUNT(M) as T1, M FROM T2 WHERE ID=1 GROUP BY L
asked Nov 27, 2019 alecxe 7.5k points
0 votes
2 answers 117 views
117 views
Problem : I am new to the SQL. What am I doing wrong here? SELECT LEFT(SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000), PATINDEX('%[^0-9]%', SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%',batchinfo. ... I am trying to do group by the first, second, and third columns having the max rid. It is works perfectly fine without the group by and having.
asked Nov 27, 2019 alecxe 7.5k points
0 votes
2 answers 187 views
187 views
Problem : I know that my query below causes the error - ORDER BY items must appear in the select list if SELECT DISTINCT is specified. SELECT DISTINCT mycity FROM Employees WHERE mycountry = N'USA' AND myregion = N'WA' ORDER BY mybirthdate; What is a reason for ... gives me some output, but not the error. Is it because SELECT DISTINCT mycity only gives the result set with cities and nothing else ?
asked Jan 17, 2020 jwilliam 3.9k points
0 votes
1 answer 9 views
0 votes
1 answer 20 views
20 views
Problem: I am a na&iuml;ve to Java, Jsp, Servlet and trying to make a very small registration program with the database. But while trying to run this I am facing below errors: HTTP Status 405 - HTTP method GET is not supported by this URL type Status report message HTTP ... </welcome-file>     </welcome-file-list> </web-app> I am looking for Java expert who can help me in fixing above error.
asked Jul 29, 2020 Raphael Pacheco 4.9k points