I need to aggregate the total for when the
case when statement returns true. But I need to do this per partition. My data looks something like this:
Product|Variation_code|Total_product_variations|Discount_code A | x21 | 3 | OFF A | x22 | 3 | LIQ A | x23 | 3 | NON B | x4 | 2 | LIQ B | x9 | 2 | LIQ C | x34 | 4 | C | X36 | 4 | OFF C | x39 | 4 | NON C | x37 | 4 | OFF
I want the total count per product where Discount_code = ‘LIQ’
I have tried
CASE WHEN(Discount_code = 'LIQ' then 1 else 0 end) AS total_liq but it returns 1 or 0 in each row.
COUNT(CASE WHEN Discount_code = 'LIQ' THEN 1 ELSE 0 END)AS total_liq returns a bunch of single 1’s like so:
Product|Variation_code|Total_product_variations|Discount_code | total_liq A | x21 | 3 | LIQ | 1 A | x22 | 3 | LIQ | 1
There is a group by 1,2,3,4,5 at the end of each of these. How do I go about achieving this?
I can’t roll variation_code into product or filter as I need to be able to see them later.