5

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 
    ON p.id = t.item 
WHERE t.user = 1 
GROUP BY t.item
ORDER BY t.created DESC;

and tables:

Payments:

id     item   user   created
============================
1      1      1      2017-01-10
2      2      1      2017-01-11
3      3      1      2017-01-12
4      4      1      2017-01-13
5      1      1      2017-01-14

Products:

id     title    created
==========================
1      First     2016-12-10
1      Second    2016-12-11
1      Third     2016-12-12
1      Fourth    2016-12-13

The final result should look lie:

Name    Total
First   2
Second  1
Third   1
Fourth  1

But if I change my query to GROUP BY t.item, t.created Error is gone, but I end up with five records instead of four, which is not what I want. Since I'm grouping items based on "item" field, there should be only four records

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Alko
  • 1,421
  • 5
  • 25
  • 51
  • Why do you count `t.qty`? With an outer join on `products` you should rather be counting a column in `products`, e.g. `COUNT(p.id)`, so as to count zero when there is no match. – Thorsten Kettner Feb 05 '17 at 20:26
  • I suppose the IDs in table `products` are really 4-3-2-1 and not 1-1-1-1? (So 'First' is item 4 which has the latest `created` date in `payments` and comes hence first when ordering by `t.created DESC` as shown by you in the desired result.) – Thorsten Kettner Feb 05 '17 at 20:32

3 Answers3

12

This is your query:

SELECT p.title, COUNT(t.qty) AS total 
-------^
FROM payments t LEFT JOIN
     products AS p 
     ON p.id = t.item 
WHERE t.user = 1 
GROUP BY t.item
---------^
ORDER BY 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 = 1 
GROUP BY p.title
ORDER BY 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).

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
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;
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
0

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 = 1 
  GROUP BY 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 = 1 
  GROUP BY p.title
  order by 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 

SET sql_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

ScaisEdge
  • 131,976
  • 10
  • 91
  • 107