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?

  • 1
    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. Dec 19 '19 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. Dec 19 '19 at 0:50
  • @ErwinBrandstetter Reopened
    – Paul White
    Dec 19 '19 at 12:38
  • Thanks, @PaulWhitesaysGoFundMonica! Dec 20 '19 at 0:02

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}')

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.

  • 1
    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! Dec 20 '19 at 8:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.