Category with zero value required in group by result

Question

I'm using SQL SS with read-only permission. The data I'm using has 3 product types, but I need a result displaying a sum of transactions for 6 product types with zero values for the 3 not available:

Example result:

Product Type	Total Transactions
Product Type 1	£500,000
Product Type 2	£1,000,000
Product Type 3	£0.00
Product Type 4	£750,000
Product Type 5	£0.00
Product Type 6	£0.00

Product Type 3, 5 , and 6 do not exist in the table and therefore will always be zero but need to be in the result. All Product Types also need to be renamed for the criteria.

I have tried a Case expression with a group by product type which works for the 3 available product types, this is what I have that works:

SELECT
CASE
WHEN Product Type = 'XYZ' AND Criteria1 = 'Y' THEN 'Product Type 1'
WHEN Product Type = 'ABC' AND Criteria1 = 'Y' THEN 'Product Type 2'
WHEN Product Type = 'DEF' AND Criteria1 = 'N' THEN 'Product Type 4'
END as "Product Type",

sum(Transactions) as "Total Transactions"

from ProductTable inner join TransactionTable on ProductCode = TransactionProductCode
where TransactionDate BETWEEN 'Date1' and 'Date2'

GROUP BY
CASE
WHEN Product Type = 'XYZ' AND Criteria1 = 'Y' THEN 'Product Type 1'
WHEN Product Type = 'ABC' AND Criteria1 = 'Y' THEN 'Product Type 2'
WHEN Product Type = 'DEF' AND Criteria1 = 'N' THEN 'Product Type 4'
END

Any advice to achieve the example result would be much appreciated, thank you!

Answer 1

You should use a left join and move your date criteria to the join clause. You can also coalesce your amount when summing to return a 0 rather than null.

So it's something like this:

select 
 --case expression here,
 sum(coalesce(t.amount, 0)) as total_transactions
from products p 
left join transactions t 
  on p.productId = t.productId 
 and t.some_date between date1 and date2
group by --case expression