Select things from three different tables with inner join

Question

Well, I have three tables:

Cart:

ID | Product | Quantity
1    S1FG      3
1    C0K0      1
1    SLM1      6
2    S1FG      2
2    S94A      2

Products:

Code | Price
S1FG   10.00
C0K0   8.00
SLM1   19.50
S94A   2.00

And Users:

ID | Nickname
1    Mark
2    Steve

I want this:

Nickname | Products | Total
Mark       10         155.00
Steve      4          24.00

In words, I want a list of users that has something in Cart, a count of their items and a sum of the prices of their items.

I tried this:

SELECT DISTINCT b.Nickname, SUM(a.Quantity) as Products, SUM(a.Quantity*c.Price) as Total 
FROM Cart a 
INNER JOIN Users b ON (a.ID = b.ID) 
INNER JOIN Products c ON (a.Product = c.Code)

But it didn't work...

What can I do?

Answer 1

You need to GROUP BY the nickname or you'll just get a single line:

SELECT DISTINCT b.Nickname, SUM(a.Quantity) as Products, SUM(a.Quantity*c.Price) as Total 
FROM Cart a 
INNER JOIN Users b ON (a.ID = b.ID) 
INNER JOIN Products c ON (a.Product = c.Code)
GROUP BY b.Nickname