Search code examples
t-sqlcountdivide

TSQL divide one count by another to give a proportion

I would like to calculate the proportion of animals in column BreedTypeID with a value of 1. I think the easiest way is to count the n BreedTypeID = 1 / total BreedTypeID. (I also wnat them to have the same YearDOB and substring in their ID as shown) I tried the following:

(COUNT([dbo].[tblBreed].[BreedTypeID])=1 OVER (PARTITION BY Substring([AnimalNo],6,6), YEAR([DOB]))/ COUNT([dbo].[tblBreed].[BreedTypeID]) OVER (PARTITION BY Substring([AnimalNo],6,6), YEAR([DOB]))) As Proportion

But it bugged with the COUNT([dbo].[tblBreed].[BreedTypeID])=1

How can I specify to only count [BreedTypeID] when =1?

Many thanks

Solution

  • This will fix your problem, although I would suggest you use table aliases instead of schema.table.column. Much easier to read:

    Just replace:

    COUNT([dbo].[tblBreed].[BreedTypeID])=1

    WITH

    SUM( CASE WHEN [dbo].[tblBreed].[BreedTypeID] = 1 THEN 1 ELSE 0 END)