-2

I m facing an issues in sql query It worked with previous version - 5.6

But now its not working with version 5.7
So anyone can help me to convert this query to sql 5.7

SELECT to_startdate, to_enddate  
FROM tour 
WHERE  to_name !='' 
   AND to_startdate !='0000-00-00' 
   AND to_deactivated !=1 
GROUP BY MONTH(to_startdate), YEAR(to_startdate) 
ORDER BY to_startdate
Ryan Nghiem
  • 2,417
  • 2
  • 18
  • 28
Mandeephub
  • 31
  • 4
  • 1
    Share the error log, if any. – chaitan64arun May 07 '19 at 07:05
  • 1
    5.6 and 5.7, what is that? MySQL versions? – jarlh May 07 '19 at 07:06
  • 1
    Why GROUP BY when no aggregate functions are involved? – jarlh May 07 '19 at 07:07
  • @jarlh yes im checking this by "select @@version" – Mandeephub May 07 '19 at 07:08
  • @jarlh i need to show these values my month and year – Mandeephub May 07 '19 at 07:10
  • sample data and output is helpful – Zaynul Abadin Tuhin May 07 '19 at 07:11
  • @ZaynulAbadinTuhin ????? – Mandeephub May 07 '19 at 07:15
  • Please post the error message you are getting so that we don't have to guess what the problem is. – Nick May 07 '19 at 07:20
  • 1
    My guess is that the `ONLY_FULL_GROUP_BY ` setting has been changed by the upgrade but who really knows as long as OP refuses to post the error message or further explain why the query doesn't work.. – Joakim Danielson May 07 '19 at 08:25
  • @Nick MySQL said: Documentation #1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'jungle_search.tour.to_startdate' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by this is the error im getting when I run query – Mandeephub May 07 '19 at 08:55
  • @Mandeep this will probably help: https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql – Nick May 07 '19 at 08:58
  • Please provide sample data, desired results, and an explanation of what you want the code to do. – Gordon Linoff May 07 '19 at 11:37
  • @GordonLinoff expected result :- https://websearchsolutions.in/url/junglelore/tripcalendar.php Result which I'm Getting :- http://junglelore.net/tripcalendar.php Same query is used on both :- "SELECT to_startdate,to_enddate FROM tour WHERE to_name !='' AND to_startdate !='0000-00-00' AND to_deactivated !=1 GROUP BY MONTH(to_startdate),YEAR(to_startdate) ORDER BY to_startdate" – Mandeephub May 07 '19 at 11:44

1 Answers1

0

Your query doesn't make sense in general -- the select references columns and these are not aggregated. You can use aggregation functions. I don't know what values you want, but something like this:

SELECT MIN(to_startdate), MIN(to_enddate)  
FROM tour 
WHERE to_name <> ''  AND
      to_startdate <> '0000-00-00' AND
      to_deactivated <> 1 
GROUP BY MONTH(to_startdate), YEAR(to_startdate) 
ORDER BY MIN(to_startdate)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • `SELECT MIN(to_startdate), MIN(to_enddate) FROM tour WHERE to_name !='' AND to_startdate !='0000-00-00' AND to_deactivated !=1 GROUP BY MONTH(to_startdate), YEAR(to_startdate) ORDER BY to_startdate` result #1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'jungle_search.tour.to_startdate' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by – Mandeephub May 07 '19 at 11:50
  • @Mandeep . . . I fixed the `ORDER BY`. – Gordon Linoff May 07 '19 at 11:53