MySQL SUM、INNER JOIN 和 GROUP BY 在同一条语句中。语法错误

Question

我需要从这四个表中获取每个艺术家的总销售额:

Artists (artist.id, artist.name)
Events (events.id, events.title, events.artist_id)
Sales (sales.events_id, sales.buyer_id, sales.amount)
Buyer (buyer.id, buyer.name)

我需要以下语句连接三个表:

SELECT sales.amount as SalesTotal, artists.title AS Artist
SUM (SalesTotal) 
FROM sales
INNER JOIN events ON events.id = sales.event_id
INNER JOIN artists ON artists.id = events.artist_id
GROUP BY Artist

但我总是遇到语法错误。我用谷歌搜索了它，但我得到的所有示例都在同一张表中。我不知道错误应该在哪里。任何帮助将不胜感激

Answer 1

我想你想要这个:

SELECT 
     SUM(sales.amount) as SalesTotal, 
     artists.title AS Artist
FROM sales
INNER JOIN events ON events.id = sales.event_id
INNER JOIN artists ON artists.id = events.artist_id
GROUP BY artists.title;

您不能在 group by 子句中使用别名，因为语句中不同子句的评估或执行顺序不同。 group by 子句在应用列列表中的别名定义之前执行。