Mysql函数求和错误[重复]

Question

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

我得到错误：

#1140-在没有GROUP BY的聚合查询中，SELECT列表的表达式#1包含非聚合列'SAKILA_TAK14.CUSTOMER.FIRST_NAME'；这与sql_mode=only_full_group_by不兼容

Answer 1

V5.7.5及以上版本的MySQL实现了对聚合函数的更严格的使用&在select列表和ORDER BY列表（或HAVING子句）中不允许非聚合列。参见:https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

此外，您也不能按amount列排序&得到您期望的顺序（如果您将setting ONLY_FULL_GROUP_BY设置为false选项）--因为您已经对其进行了求和&结果列将不再称为amount--您需要使用别名。

您需要按名字和姓氏分组：

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;

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;