Count / Avg of Yes or No - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Count / Avg of Yes or No

Author

Topic

javigarza
Starting Member

2 Posts

Posted - 2013-02-13 : 12:36:33

Hey guys,I need some help.

I have a column with Yes or NO values. I'm needing to get a percentage of the total numner of yes's in relation to the total number of records.

The column is varchar datatype. I'm really lost on what to do. Any help would be appreciated.

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-13 : 12:48:02

[code]
SELECT COUNT(CASE WHEN column = 'Yes' THEN 1 END) * 100.0/COUNT(*) AS [yes%],
COUNT(CASE WHEN column = 'No' THEN 1 END) * 100.0/COUNT(*) AS [No%]
FROM table
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

javigarza
Starting Member

2 Posts

Posted - 2013-02-13 : 16:05:06

thanks for your help. That seemed to do the trick...

I've been trying to modify the script some:

I have need to pull the same data but with a filter on the same report.

In the last line, i'm tryng to filter by Female only. What am i doing wrong?

this is what i have so far:

select * from Staar

select COUNT(*) [Student_Count] , avg([Scale Score])[Scale Score]

,COUNT(case when [Level 2 (Satisfactory) Initial] = '1' then 1 end) * 100.0/COUNT(*) as [Level 2 Initial]
,COUNT(case when [Level 2 (Satisfactory)] = '1' then 1 end) * 100.0/COUNT(*) as [Level 2 (Satisfactory)]
,COUNT(case when [Commended Advanced] = '1' then 1 end) * 100.0/COUNT(*) as [Commended Advanced]
,COUNT(case when [Level 2 (Satisfactory) Initial] = '1' then 1 end) * 100.0/COUNT(*) as [Level 2 Initial] where exists ( select * from staar where [Gender (DF)] = 'Female')

from Staar

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-13 : 23:27:08

you just need this


select COUNT(*) [Student_Count] , avg([Scale Score])[Scale Score] 

,COUNT(case when [Level 2 (Satisfactory) Initial] = '1' then 1 end) * 100.0/COUNT(*) as [Level 2 Initial]
,COUNT(case when [Level 2 (Satisfactory)] = '1' then 1 end) * 100.0/COUNT(*) as [Level 2 (Satisfactory)]
,COUNT(case when [Commended Advanced] = '1' then 1 end) * 100.0/COUNT(*) as [Commended Advanced]
,COUNT(case when [Level 2 (Satisfactory) Initial] = '1' then 1 end) * 100.0/COUNT(*) as [Level 2 Initial] where exists ( select * from staar where [Gender (DF)] = 'Female')

from Staar 
WHERE [Gender (DF)] = 'Female'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources