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?
CRETE TABLE
statements). That tells more than a thousand words.