I need to multiply values from two tables and sum results. The database is either Postgres or MSSQL (long story).
Table Bought_Products
ID | Name | Customer_ID | Product_ID | Foo | Bar | Blarg
1 | test | 123 | 321 | NULL| 1 | NULL
2 | tast | 123 | 231 | 5 | NULL| 20
3 | tost | 456 | 321 | NULL| 3 | NULL
Table Price
ID | Item | Product_ID | Cost
1 | Foo | 321 | 10
2 | Bar | 321 | 5
3 | BASE | 321 | 100
4 | BASE | 231 | 50
5 | Blarg| 231 | 0.5
6 | Foo | 231 | 1
I need a result like this:
Customer | Products | Revenue
123 | 321, 231 | 170
For Customer 123 there are 2 products: 321 and 231, with a base price of 100 and 50 respectively; plus 1 x 'Bar' item at 5; plus 5 x 'Foo' items at 1; and 20 x 'Blarg' items at 0.5.
This is what I have for a start:
select sum(p.cost) from price p
join Bought_Products bp on bp.product_id = p.product_ID
where p.item = 'BASE'
I need to add the relevant 'Foo', 'Bar' or 'Blarg' values from Bought_Products
multiplied with the respective Price.Cost
(matching the column header to the item).
I've not used basic mathematical functions in SQL, yet. I'm not sure how to join between the 2 tables to then multiply the values. With subqueries and subsequent joins?
Best Answer
This query produces your desired result for every customer in the table:
db<>fiddle here
We only need to join once. The magic happens in the
CASE
expression to multiply depending on theItem
.I made it a
LEFT [OUTER] JOIN
to keep every product in the result even without any matching price rows. Use[INNER] JOIN
to remove those instead. And/or activate the commentedWHERE
clause to the same effect.The row with
ID 1
in thePrice
table stands out, btw, as the related row inBought_Products
hasFoo IS NULL
.The relational design seems a bit mysterious. I hope you have good reasons.