Count - Over Partition and Case

Bone_12 361 Reputation points


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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,911 questions
0 comments No comments
{count} votes

Accepted answer
  1. Bart 151 Reputation points

    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'

    0 comments No comments

0 additional answers

Sort by: Most helpful