CODEWITHSUNDEEP
mysqlsuminner-join
Rafael Munoz
Rafael Munoz

Reputation: 656

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

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

Upvotes: 0

Views: 40

Answers (2)

Ashu
Ashu

Reputation: 1320

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

Upvotes: 1

Mithrandir
Mithrandir

Reputation: 25357

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.

Upvotes: 2

Related Questions