What is a ‘clean’ way of getting the output required?


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.

Similarly, 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.