Multiply several discrete values in multiple columns and then sum

Question

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?

Answer 1

This query produces your desired result for every customer in the table:

SELECT customer_id AS customer
     , string_agg(DISTINCT product_id::text, ', ') AS products
     , sum(cost * CASE p.item
                     WHEN 'BASE'  THEN 1
                     WHEN 'Foo'   THEN b.foo
                     WHEN 'Bar'   THEN b.bar
                     WHEN 'Blarg' THEN b.blarg
                  END) AS revenue
FROM   bought_products b
LEFT   JOIN price p USING (product_id)
-- WHERE  p.item = ANY ('{BASE,Foo,Bar,Blarg}')
GROUP  BY 1;

db<>fiddle here

We only need to join once. The magic happens in the CASE expression to multiply depending on the Item.

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 commented WHERE clause to the same effect.

The row with ID 1 in the Price table stands out, btw, as the related row in Bought_Products has Foo IS NULL.

The relational design seems a bit mysterious. I hope you have good reasons.