Mysql 5.7 Order By Clause Is Not In Group By Clause And Contains Nonaggregated Column

I'm trying to figure out without disabling 'only_full_group_by' in my.ini here is my query: SELECT p.title, COUNT(t.qty) AS total FROM payments t LEFT JOIN products AS p

Solution 1:

This is your query:

SELECT p.title, COUNT(t.qty) AS total 
-------^FROM payments t LEFTJOIN
     products AS p 
     ON p.id = t.item 
WHERE t.user =1GROUPBY t.item
---------^ORDERBY t.created DESC;
---------^

The pointed to places have issues. Notice that the SELECT and GROUP BY are referring to different column. In a LEFT JOIN, you (pretty much) always want to aggregate by something in the first table, not the second.

The ORDER BY is another problem. You are not aggregating by this column, so you need to decide which value you want. I am guessing MIN() or MAX():

SELECT p.title, COUNT(t.qty) AS total 
FROM payments t LEFT JOIN
     products AS p 
     ON p.id = t.item 
WHERE t.user = 1GROUPBY p.title
ORDERBY MAX(t.created) DESC;

I will also add that COUNT(t.qty) is suspect. Normally qty refers to "quantity" and what you want is the sum: SUM(t.qty).

Solution 2:

There are two t.created for item 1. So decide by which you want to sort. E.g.:

ORDER BY MIN(t.created) DESC;

Solution 3:

For me you query should by group by p.title

SELECT 
    p.title,
    COUNT(t.qty) AS total 
  FROM
    payments t 
    LEFT JOIN products AS p 
      ON p.id = t.item 
  WHERE t.user = 1GROUPBY p.title;

or

SELECT 
    p.title,
    COUNT(t.qty) AS total 
  FROM
    payments t 
    LEFT JOIN products AS p 
      ON p.id = t.item 
  WHERE t.user = 1GROUPBY p.title
  orderby p.created;

then for "But if I change my query to GROUP BY t.item, t.created Error is gone" remember that starting from mysql 5.7 if you use selected column not in group by you have an error ..

If you really need you can disable using a proper set for sql mode eg

SETsql_mode=''

https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

And last the column in order don't have effect on group by and on ONLY_FULL_GROUP_BY param .. but if column i order by is not in select have pratically no sense