# Multiply several discrete values in multiple columns and then sum

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?

• With the added description, I think I get what you are after. I trimmed a lot of noise. Feel free to clarify. Typically such a question should disclose the RDBMS and version (not throw in two different brands, that's not very helpful to the general public, we are not too fond of that. Rather chose one RDBMS. And provide (minimum) exact table definitions showing data types and constraints (`CRETE TABLE` statements). That tells more than a thousand words. Commented Dec 19, 2019 at 0:21
• I'm at heart a Pragmatist, the Database is in Postgres, but there is a Mirror copy in MSSQL, my primary preference is MSSQL but if it is easier to accomplish in Postgres, then I'm happy to use Postgres. The 2 key parts of the question are matching column1 to row value1, in order to multiply the values in column1 and row value2, doing that N number of times for a given customer ID then summing the value together. Appreciate the help in editing - I blame the festive period. Commented Dec 19, 2019 at 0:50
• @ErwinBrandstetter Reopened Commented Dec 19, 2019 at 12:38
• Thanks, @PaulWhitesaysGoFundMonica! Commented Dec 20, 2019 at 0:02

## 1 Answer

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.

• That's so infuriatingly simple! Thank you for the answer! Plus that DB Fiddle thing is quite nifty! As for your last comment, Yes, In my opinion the design of the DB is 'Mysterious' to put it mildly, It's why in my first draft I mentioned that it was legacy, we are trying to get off of it and the Dev who wrote it is not contactable. But anyways - appreciate the help! Commented Dec 20, 2019 at 8:55