Stefan Stefan - 4 months ago 7
MySQL Question

MySQL adding columns 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 returns:

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