Aggregated query without GROUP BY

by

A change was made in version 5.7.5 where it will now, by default, reject queries in which you aggregate using a function (sum, avg, max, etc.) in the SELECT clause and fail to put the non-aggregated fields in the GROUP BY clause. This behavior is part and parcel to every other RDBMS and MySQL is finally jumping on board.

You have two options:

  1. You can change the MySQL settings to default to the old behavior to allow not-so-great queries like this. Information can be found here
  2. You can fix your query

Option 2 would look something like:

SELECT id, password, COUNT(id) AS count FROM users WHERE email = :email GROUP BY id, password LIMIT 1

It’s also important to note that excluding a non-aggregated column from the GROUP BY clause is permitted in 5.7.5 and newer version in the event that the unaggregated column has been limited to a single value (such as a filter in the WHERE clause). See the link above for examples of this allowed exception.

More Related Contents:

  1. How can create Database using url or other method [duplicate]
  2. Problame with files on SQL
  3. Im trying to create some temporary tables in mysql using php
  4. How can I prevent SQL injection in PHP?
  5. SQL injection that gets around mysql_real_escape_string()
  6. How to include a PHP variable inside a MySQL statement
  7. How to apply bindValue method in LIMIT clause?
  8. SELECT COUNT(*) AS count – How to use this count
  9. Achieve hierarchy, Parent/Child Relationship in an effective and easy way
  10. selecting unique values from a column
  11. How to search for slash (\) in MySQL? and why escaping (\) not required for where (=) but for Like is required?
  12. I cannot get my login form to connect interact properly with mySQL database [closed]
  13. mysql_num_rows(): supplied argument is not a valid MySQL result resource [duplicate]
  14. Increment value in MySQL update query
  15. Create a dynamic mysql query using php variables
  16. How to separate DATE and TIME from DATETIME in MySQL?
  17. Do SQL connections opened with PDO in PHP have to be closed
  18. MYSQL Select from tables based on multiple rows
  19. How to execute raw queries with Laravel 5.1?
  20. How to convert ISO8601 to Date format in php
  21. MySQL skipping first row
  22. How to reverse order output of a MySQL query
  23. How do you do multiple SQL statements in one mysql_query?
  24. MySQL/SQL retrieve first 40 characters of a text field?
  25. Fatal error: Call to a member function query() on null
  26. How would I implement a simple site search with php and mySQL?
  27. Do I have to use mysql_real_escape_string if I bind parameters?
  28. SELECT row by DATEPART()
  29. PDO and MySQL ‘between’
  30. MySQL Join Multiple Rows as Columns

Leave a Comment