SQL Server Forums - Count / Avg of Yes or No

Home | Weblogs | Forums | SQL Server Links

Active Forum Topics | Popular Articles | All Articles by Tag | SQL Server Books | About

SQL Server Forums

Register Now and get your question answered!

Username:	Password:
Save Password
Forgot your Password?

All Forums

General SQL Server Forums

New to SQL Server Programming

Count / Avg of Yes or No

New Topic

Reply to Topic

Printer Friendly

Author

Topic

javigarza
Starting Member

2 Posts

Posted - 02/13/2013 : 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

India
47036 Posts

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


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

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

javigarza
Starting Member

2 Posts

Posted - 02/13/2013 : 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

India
47036 Posts

Posted - 02/13/2013 : 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/

Edited by - visakh16 on 02/13/2013 23:27:28

Topic

New Topic

Reply to Topic

Printer Friendly

Jump To:

SQL Server Forums

This page was generated in 0.05 seconds.