Stefan Stefan - 4 months ago 10
SQL Question

MySQL adding collums with a particular id

I have three tables.
One is some kind of a customer table where id and name is stored.

The second one is a item table where id, name and price is stored.

The third one combines ids of both with a quantity.

My question is how I can SELECT a name of a customer with a total price of all items he want to buy.

SELECT first.name, third.quantity * second.price from first
left join third on third.fID = first.ID
left join second on second.ID = third.sID ;


This what I have. It selects all items with a total price but I need to add them for every customer.

e.g. it gives me:

customer1 5000
customer2 100
customer1 1000


but I want:

customer1 6000
customer2 100

Answer

You seem to want an aggregation query with a GROUP BY:

SELECT first.name, SUM(third.quantity *  second.price)
FROM first LEFT JOIN
     third 
     ON third.fID = first.ID LEFT JOIN
     second 
     ON second.ID = third.sID
GROUP BY first.name;
Comments