mysql 5.7 query with group by error

0
votes
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.

2
mysqlgroup-bysql-mode
add titleen in the group by – Roeland Van Heddegem
I need only group by caten! – Lilit Tarposhian
what are you trying to achieve ? – Ravi
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

2 Answers

2
votes

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.

0
votes

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;