0

From https://www.percona.com/blog/2019/05/13/solve-query-failures-regarding-only_full_group_by-sql-mode/

“Hey, what’s going on with my applications? I installed a newer version of MySQL. I have queries that perfectly run with the older version and now I have a lot of errors.”

This is a question some customers have asked me after upgrading MySQL. In this article, we’ll see what one of the most frequent causes of this issue is, and how to solve it.

We are talking about this error:

My error is the same:

In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'ebenlizy_inventorycontrol.sales_orders.orderId'; this is incompatible with sql_mode=only_full_group_by

my query is

$query=mysqli_query($conn,"
  SELECT
     Sum(sales_order_details.quantity) AS Quantity,
     (SELECT buyingPrice from items INNER JOIN sales_order_details ON items.stockId = sales_order_details.stockId WHERE sales_order_details.orderId = sales_orders.orderId) AS buyingPrice,
 sales_order_details.price
  FROM sales_orders 
  JOIN sales_order_details ON sales_orders.orderId = sales_order_details.orderId  
  WHERE MONTH(sales_orders.orderDate)='$month' 
    and YEAR(sales_orders.orderDate)='$year' 
    AND sales_orders.branchId =$id 
    AND salesStatus=1") 
or die(mysqli_error($conn));
Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Edgar Inga
  • 21
  • 2
  • please provide some sample data and your desired output in table format – Fahmi Oct 19 '20 at 05:06
  • 1
    I'm curious; the start of your question is written like a blog post. Are you writing a blog post about this? Did you get half way through your blog post and then realize you didn't actually understand the subject matter so you hit up SO with a question? Does the author of any answer here get a credit in this blog as being the person who supplied the info, or will it be presented as your work? If it's being credited with a link to here then I'll I undelete my answer. If SO contributions are being used as source material without credit, that's a poor show, and I'll leave it deleted – Caius Jard Oct 19 '20 at 05:32
  • The aggregation part is not the only issue in your query. There is also the subquery in the select clause that is likely to return more than one row. – Thorsten Kettner Oct 19 '20 at 05:53
  • It is a very bad idea to use `die(mysqli_error($conn));` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) – Dharman Oct 19 '20 at 11:52

0 Answers0