MySQL SUM, INNER JOIN and GROUP BY in the same statement. Syntax error

Question

I need to get the total amount of sales of each artist out of this four tables:

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)

I need the following statement joining just three tables:

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

But I get always a Syntax error. I have googled it but all examples I get are inside the same table. I do not get where the error should be. Any help would be appreciated

Actually it needs to be `SUM(sales.amount) as SalesTotal, artists.title AS Artist` — Salman A, Feb 15 '19 at 08:44

score 2 · Accepted Answer · answered Feb 15 '19 at 08:46

2

I think you want this:

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;

You can't use the alias names in the group by clause, because the order of evaluation or execution of the different clauses in your statement. The group by clause is executed bevor the alias definitions in the column list are applied.

answered Feb 15 '19 at 08:46

Mithrandir

24,869
6
50
66

you can use alias in group by clause – Harshil Doshi Feb 15 '19 at 08:49
1

@HarshilDoshi well, not quite https://stackoverflow.com/questions/3841295/sql-using-alias-in-group-by, some systems allow it. But it should not work and it should not be used! – Mithrandir Feb 15 '19 at 08:51
sorry. I got confused with `order by` clause. Thanks! – Harshil Doshi Feb 15 '19 at 08:55
It will give you out put with only those artist who has sales... it wont result null artist sales – Ashu Feb 15 '19 at 08:57

score 1 · Answer 2 · answered Feb 15 '19 at 09:01

It will return you all artist list with there total sale + null artist:

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

Output :

Artist   SaleTotal
abc      2000
pqr      NULL
xyz      100

MySQL SUM, INNER JOIN and GROUP BY in the same statement. Syntax error

2 Answers2