1

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.

Glorfindel
  • 2,201
  • 5
  • 17
  • 26
  • 2
    Please tag the qiestion with the rdms and version you are using – nbk Jul 15 '20 at 22:29
  • Could you please provide your table structures as DDL and your data as DML? It's unclear to me exactly what you want - you only have one listing for each product - so what's the point of doing COUNTs or SUMming? – Vérace Jul 16 '20 at 06:19
  • What is meant by Total? Are you looking for the total variations or row counts or something else? –  Jul 20 '20 at 00:39
  • And please add your current query which you are referring to –  Jul 20 '20 at 14:07

0 Answers0