mysql 5.7 query with group by error

Question

select caten as category, titleen as title from pages where pshow = 1 group by caten ASC

getting this error:

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

How to fix the error? Can't change sql_mode.

Just for your info, you are supposed to accept and upvote correct answer and upvote other helpful answer. This expects community from everyone. Read this meta.stackexchange.com/a/5235 — Ravi, Jan 23 '18 at 16:29

score 2 · Answer 1 · answered Jan 22 '18 at 12:41

2

I'm not sure, what exactly are you trying to achieve, but you need to correct

GROUP BY caten ASC

to

GROUP BY caten ORDER BY caten ASC

Note : ASC (ascending) refers to order of value of specific column. That should be followed by ORDER BY clause.

answered Jan 22 '18 at 12:41

Ravi

30,829
42
119
173

This will not work as she is fetching titleen too without adding to the group by clause. As per the sql mode , it will not allow . – Priya Jain Jan 22 '18 at 12:45
@PriyaJain I understand your concern, but it will group by `caten`. Just open w3school and try group by example for your reference:-) Please note, she didn't used any aggregate function. So, this should be a simple select query – Ravi Jan 22 '18 at 12:47
I know how group by works. Please read the question clearly and comments too as she wants select both the column. just open google and read about sql mode for your reference :-) Also, don't be harsh in your comments, It is against stackoverflow policy. – Priya Jain Jan 22 '18 at 12:51
@PriyaJain Please open https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_groupby and execute `SELECT Country, City FROM Customers GROUP BY Country;` Let me know, if you get error or result. – Ravi Jan 22 '18 at 12:53
@PriyaJain I'm repeating again, she didn't used any aggregate method, so, it will be normal select – Ravi Jan 22 '18 at 12:55
Can you tell me the version of mysql running over there. It's something that got introduced in mysql 5.7.5 – Priya Jain Jan 22 '18 at 12:56
@PriyaJain I'm not sure about their version, and my knowledge SQL is rusty, so I'm unable to tell, which version has this issue. As I'm more into java, I just tried refreshing my SQL skill here. ;-) – Ravi Jan 22 '18 at 12:59
Now please open http://johnemb.blogspot.in/2014/09/adding-or-removing-individual-sql-modes.html and read. You will get what I am referring to. – Priya Jain Jan 22 '18 at 13:02
@PriyaJain hmm.. I see. Just to let you know, I'm not disagreeing with your point made *fetching titleen too without adding to the group by clause*. And, I'm aware of it. And that's the reason I told you first, it might be basic SQL error. But, we need to get it confirmed by executing the SQL, which executed fine in one of the platform. Although, we are not sure about its version. – Ravi Jan 22 '18 at 13:09
2

plus 1 for simple solution, however, you can just skip `ASC` because default is `ASC` – Aniket Sahrawat Jan 22 '18 at 13:10
@PriyaJain *Don't be harsh in your comments, It is against stackoverflow policy* lol, I'm not quite sure, when did I used harshed word for you – Ravi Jan 22 '18 at 13:11
1. I agree to your point that it is a basic sql error to not have an aggregate function. I was just saying that you solution is not addressing all her requirements. I cross checked with latest mysql release and got the same error. After changing the sql mode , I was not getting that error. However, the answer returned was not complete. Only one value of other field belonging to the group was returned. – Priya Jain Jan 22 '18 at 13:19
@PriyaJain Of course, It doesn't address all her requirement (even I don't know, what exactly she is trying). But, yes, AFAIK, that was syntax error and thanks for sharing details (I wasn't aware of it). – Ravi Jan 22 '18 at 13:40

score 0 · Answer 2 · answered Jan 22 '18 at 13:14

If you really want to do a group by on only caten then your query should be.

SELECT caten AS category FROM pages 
WHERE pshow = 1
GROUP BY caten 
ORDER BY caten  ASC;

Or at least aggregate all the values of titleen using the GROUP_CONCAT function for every distinct caten value.

SELECT caten AS category, GROUP_CONCAT(DISTINCT titleen ORDER BY titleen ASC SEPARATOR ',')
AS titles FROM pages 
WHERE pshow = 1
GROUP BY caten 
ORDER BY caten  ASC;

But if you really need to get titleen without concatenation, then you are supposed to add it to the group by clause.

SELECT caten AS category, titleen AS titles FROM pages 
WHERE pshow = 1
GROUP BY caten, titleen 
ORDER BY caten  ASC;

mysql 5.7 query with group by error

2 Answers2