creating a "% of total" columnin the script

[fuyuki11]

Hello.
I have Table A with columns B, C and D. Column B contain status ('Yes' or 'No'), C is a measure, and D is area ('X', 'Y' or 'Z'.

What I want to do is to work out % of Yes out of total - (C where B=Yes)/(C where B=Yes)+(C where B=No), then to be grouped by D.
So the output to look like

D (C where B=Yes)/(C where B=Yes)+(C where B=No)
---------------------------------------------
X 0.78
Y 0.50
Z 0.33

I am still new at this so I don't have a clue how to achive this. Is there a simple SQL to create output like this?

Thanks for your help.

 

[vongrunt]

Post some sample source data (columns B, C, D) and exact results you expect based on that data.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[fuyuki11]

Hi, here is some sample data (columns B, D and C). "% Yes" (out of total for each D) is the column I want to calculate and achive.

B D C % Yes
-------------------------------------------
Yes X 10 0.303030303
No X 23
Yes Y 188 0.497354497
No Y 190
Yes Z 65 0.792682927
No Z 17

 

[yelworcm]

This can be done with a case statement:

select b,
       sum(case when b = 'Yes' then 1 else 0 end)/convert(float, count(*)) as "Percent yes"
from A
group by b

 

[vongrunt]

That was close

select D, 1.0*sum(case when B='Yes' then C else 0 end)/sum(C) as [% Yes]
from myTable
group by D

You may also try this:

select T1.B, T2.D, 1.0*T1.C/T2.sumC as [%]
from myTable T1
inner join
(	select D, sum(C) as sumC
	from myTable
	group by D
) T2 on T2.D=T1.D
-- order by T1.D, T1.B desc

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)