CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
SmcMichael
SmcMichael

Reputation: 67

Sum Value1 in Table 1 WHEN Value2 exists in Table2

I have 2 tables. One Table contains a Customer_ID, Product_Code, and Price_Paid.

Table 2 is a single column with Product_Code that are specific to a certain type of Product.

There are thousands of product_codes in Table 1 but only about 500 in Table2.

I'd like to write a query that returns me the sum of Price_Paid per customer ONLY when the Product_Code exists in Table 2.

Would I need to do this in a subquery or is it possible to use a CASE statement to search the product code in Table 2 for a match in Table 1 before summing.

Thanks

Upvotes: 0

Views: 104

Answers (2)

James K. Lowden
James K. Lowden

Reputation: 7837

While there's no problem with @Eric Brandt's answer, it relies Product_Code being unique in products table. A more general solution is to use EXISTS. And I think you might want sum by product, not just customer? 

SELECT
  Customer_ID, Product_Code
  SUM(Price_Paid) AS SumOfPrice
FROM
  Table1 as t1
WHERE EXISTS (
    select 1 from Table2
    where Product_Code = t1.Product_Code
)
GROUP BY 
  Customer_ID, Product_Code;

Upvotes: 1

Eric Brandt
Eric Brandt

Reputation: 8101

[T]he sum of Price_Paid per customer ONLY when the Product_Code exists in Table 2.

Unless I'm missing something, that's just an INNER JOIN.

SELECT
  Customer_ID,
  SUM(Price_Paid) AS SumOfPrice
FROM
  Table1 as t1
  INNER JOIN
  Table2 as t2
    ON t2.Product_Code = t1.Product_Code
GROUP BY 
  Customer_ID;

Upvotes: 0

Related Questions