Join 3 tables and get a sum of product stock postgres sql-CodePudding

I have 3 tables:

Categories table

category_id	category_name
1	kitchen
2	bedroom

Suppliers table

supplier_id	supplier_name
1	amazon
2	wallmart
2	ebay

Product table

product_id	product_name	category_id	supplier_id	stock
1	bed	2	1	2
2	table	2	2	10
3	glass	1	1	4
4	plate	1	3	10
5	spoon	1	3	20

I want current state of suppliers stocks for each category of product.

Expected result:

CATEGORY	suppliers	stock
bedroom	amazon	2
kitchen	amazon	4
bedroom	wallmart	10
kitchen	ebay	30

CodePudding user response：

SELECT C.category_name,S.supplier_name,SUM(P.stock)TOTAL_STOCK
FROM PRODUCT P
JOIN Categories C ON P.category_id=C.category_id
JOIN Suppliers S ON P.supplier_id=S.supplier_id
GROUP BY C.category_name,S.supplier_name

You can try something like this

DBFiddle

CodePudding user response：

select   category_name as category
        ,supplier_name as suppliers
        ,sum(stock)    as stock
from     product p join suppliers s using(supplier_id) join categories using(category_id)
group by category_name, supplier_name

category	suppliers	stock
bedroom	amazon	2
bedroom	ebay	10
bedroom	wallmart	10
kitchen	amazon	4

Fiddle