Dfyjkt

I have 2 tables created with SQL. The first one has an unique id and a name of a provider. The second one has a product name, the amount of that product and the id of the provider.

I need a query that gives me the name of each provider and the total sum of the product amount that they have.

Ex:

CREATE TABLE IF NOT EXISTS provider 
(id int unique auto_increment primary key, 
name char(50)); 

CREATE TABLE IF NOT EXISTS product 
(id int unique auto_increment primary key, 
name char(30), 
provider_id int NOT NULL, 
amount int NOT NULL); 

Provider: (id, name) 
1 Mike 
2 Peter 
3 John

Product: (id, name, provider_id, amount) 
1 RedCar 1 100 
2 BlueCar 1 50 
3 RedCar 3 35 
4 OrangeCar 2 500 
5 GreenCar 3 250 

Query: 
Mike 150 
Peter 500 
John 285 

You need a simple LEFT JOIN with GROUP BY aggregation. LEFT JOIN will ensure to account for a provider even if he/she does not have any product.

SUM() function is used to calculate the sum of amount for a specific provider. We can use COALESCE() function to handle NULL values. SUM() will return NULL for the cases when there is no product for a provider.

SELECT 
 pvd.id, 
 pvd.name, 
 COALESCE(SUM(pdt.amount),0) AS total_amount
FROM Provider AS pvd
LEFT JOIN Product AS pdt 
 ON pdt.provider_id = pvd.id
GROUP BY pvd.id, pvd.name 

P.S. It is advisable to use Aliasing in case of multi-table queries, for code clarity (readability) and avoiding ambiguous behaviour.

P.P.S. If you don't want to list the provider with no products; you can change LEFT JOIN to INNER JOIN, and get rid of the COALESCE() function usage as well.

This is the SQL query that gives result as per you want.

SELECT pr.name,SUM(p.amount) as total_amount FROM `provider` pr LEFT JOIN `product` p ON p.provider_id = pr.id where 1 GROUP BY pr.id