1

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
Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
Cristian Pacurar
  • 13
  • 4

2 Answers2

0

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.

Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
0

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
Harry baldaniya
  • 167
  • 1
  • 11