Count - Over Partition and Case

Question

Hi,

I have a variable 'DU_Type' with the outputs being a '1' and '0'. ('1' being Yes, '0' being No)

I want to do a count, something like this, but can't quite get it to work and wondered if anyone can help please?

count(case when b.DU_Type = '1' then '1' else '0' ) over (partition by a.product,b.DU_Type ) as DU_Type_Yes

This count seems to count for both values (1 and 0). All I want is to show the total against 1 please.

Answer 1

Use sum() instead of count() and when using sum don't use quotation marks ('), like this:

sum(case when b.DU_Type = '1' then 1 else 0 end) over (partition by a.product, b.DU_Type) as DU_Type_Yes

The count() function counts all strings whether it is '0' or '1'