0
SELECT first_name, last_name, SUM(amount)
FROM customer
LEFT JOIN payment
ON customer.customer_id=payment.amount
ORDER BY amount DESC
LIMIT 10;

I get error:

#1140 - In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'sakila_tak14.customer.first_name'; this is incompatible with sql_mode=only_full_group_by

Ullas
  • 11,450
  • 4
  • 33
  • 50

2 Answers2

1

V5.7.5 & up versions of MySQL implement a stricter use of aggregate functions & non-aggregated columns are not allowed in the select list & ORDER BY list (or HAVING clause). See : https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

Also you cant order by the amount column & get your expected order (if you take the setting ONLY_FULL_GROUP_BY to false option) - as you have summed it & the resulting column will no longer be called amount - you need to use an alias.

You will either need to group by the first & last names :

SELECT first_name, last_name, SUM(amount) summedammount
FROM customer LEFT JOIN payment
ON customer.customer_id=payment.amount
GROUP BY first_name, last_name
ORDER BY summedammount DESC
LIMIT 10;

set the system variable ONLY_FULL_GROUP_BY to false, or use the ANY_VALUE function :

SELECT ANY_VALUE(first_name), ANY_VALUE(last_name), SUM(amount) summedammount
FROM customer
LEFT JOIN payment
ON customer.customer_id=payment.amount
ORDER BY summedammount DESC
LIMIT 10;
PaulF
  • 6,673
  • 2
  • 18
  • 29
  • Identification of this feature/change in 5.7.5 and up is good to know! Nice to see the extended Group By clause in earlier versions of MySQL is being revisited. – xQbert Nov 07 '16 at 14:33
0

You've made amount an aggregate, so you need to group all of the non-aggregate fields. Try this:

SELECT first_name, last_name, SUM(amount)
FROM customer LEFT JOIN payment
ON customer.customer_id=payment.amount
GROUP BY first_name, last_name
ORDER BY amount DESC LIMIT 10;
MyiEye
  • 445
  • 1
  • 4
  • 14