Percentage of occurrences of a value

I want to be able to retrieve the percentage that one value occurs in a field in a group. For example, a field that has either "Yes" or "No", if there are 3 "Yes" values and 1 "No" value (using mixed real and fake SQL..)

SELECT DATEPART(m,datefield), PECENTAGEOFAVALUE(YesNoField, 'Yes')
FROM sampletable
GROUP BY DATEPART(m,datefield)
Result
75

How can I do this? I know I can get the denominator by just doing a COUNT(*), but how do I count "Yes" only?

Thanks,
Kayda

Here it is..

Code Snippet

Create Table #data (

[Id] int ,

[Response] Char

);

Insert Into #data Values('1','Y');

Insert Into #data Values('2','N');

Insert Into #data Values('3','Y');

Insert Into #data Values('4','Y');

Insert Into #data Values('5','N');

Insert Into #data Values('6','N');

Insert Into #data Values('7','N');

Select

Isnull(Sum(Case When Response='Y' Then 1 End),0) / Sum(1.0) *100,

Isnull(Sum(Case When Response='N' Then 1 End),0) / Sum(1.0) *100

From

#Data

Code Snippet

--For Your query

SELECT

DATEPART(m,datefield)

Isnull(Sum(Case When YesNoField='Yes' Then 1 End),0) / Sum(1.0) * 100 PecentageofYes,

Isnull(Sum(Case When YesNoField='No' Then 1 End),0) / Sum(1.0) * 100 PecentageofNo

FROM

sampletable

GROUP BY

DATEPART(m,datefield)

|||

Here is an example:

Code Snippet

create table #t (

c1 datetime not null,

c2 char(1) not null

);

insert into #t values('20070101','y');

insert into #t values('20070115','n');

insert into #t values('20070125','y');

insert into #t values('20070205','y');

insert into #t values('20070217','n');

insert into #t values('20070225','n');

insert into #t values('20070227','n');

;with sums

as

(

select

convert(char(6), c1, 112) as ym,

sum(case when c2 = 'y' then 1 else 0 end) as sum_y,

sum(case when c2 = 'n' then 1 else 0 end) as sum_n,

count(*) over() as cnt

from

#t

group by

convert(char(6), c1, 112)

)

select

ym,

(sum_y * 100.00) / nullif(cnt, 0) as avg_y,

(sum_n * 100.00) / nullif(cnt, 0) as avg_n

from

sums

order by

ym

drop table #t

go

AMB