MySQL Error 1055 GROUP BY clause

Good morning,

We have a website working for a couple of years, but recently started to send an error message with some queries.
Here you can see one of our queries:
SELECT Books.BookID, Books.BookISBN, Books.BookTitle, Books.BookPublisher, Books.BookPoints, COUNT(*) AS Total FROM Books, Quotations WHERE Quotations.Status = ‘1’ AND Quotations.BookID=Books.BookID GROUP BY Books.BookTitle ORDER BY Books.BookPublisher, Books.BookID

I’d appreciate you can let me know how should I write the query in order it it working again, and a brief explanation in simple words about why this error message is now appearing.

Many thanks!

Can you post the complete error message?

Hello ronpat,

Many thanks for your answer.
Here the error message:

MySQL said:
#1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘libroman_db2013.Books.BookID’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Thanks again,
Arcturus

Thank you for posting the error message.

I am not a database person, but the two pieces of information that you have provided should give one of our database wizards some actionable insight into the problem.

Someone should be along soon. :coffee:

Fantastic, many thanks.

your query (reformatted) –

SELECT Books.BookID
     , Books.BookISBN
     , Books.BookTitle
     , Books.BookPublisher
     , Books.BookPoints
     , COUNT(*) AS Total 
  FROM Books
     , Quotations 
 WHERE Quotations.Status = ‘1’ 
   AND Quotations.BookID=Books.BookID 
GROUP 
    BY Books.BookTitle 
ORDER 
    BY Books.BookPublisher
     , Books.BookID

your query, syntax error corrected and best practice for inner joins substituted –

SELECT Books.BookID
     , Books.BookISBN
     , Books.BookTitle
     , Books.BookPublisher
     , Books.BookPoints
     , COUNT(*) AS Total 
  FROM Books
INNER
  JOIN Quotations 
    ON Quotations.BookID = Books.BookID 
   AND Quotations.Status = '1'  
GROUP 
    BY Books.BookID
     , Books.BookISBN
     , Books.BookTitle
     , Books.BookPublisher
     , Books.BookPoints 
ORDER 
    BY Books.BookPublisher
     , Books.BookID

I wouldn’t call myself a database wizard, but this error message is definitely something you should look into.

As a “feature”, MySQL “extends” the SQL standard and allows what in other databases would be an illegal query.

Unfortunately, this “feature” introduces the risk of the query returning nondeterministic values.

https://dev.mysql.com/doc/refman/5.5/en/group-by-handling.html

You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are nondeterministic.

oh, and if you want all books listed, even if they don’t have any status 1 quotations, change INNER to LEFT OUTER

Many, many thanks!!!

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.