Stefan Stefan - 1 year ago 63
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, 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 Source

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

SELECT, SUM(third.quantity *  second.price)
     ON third.fID = first.ID LEFT JOIN
     ON second.ID = third.sID
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download