asked on

SQL Server 2014 - Percentage

Hi Experts,

I have this bit of code:

Count(*) / Cast( SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END)  as float) *100 as PercentCompliant

Open in new window

But that is wrong:

Example:
If 92 students takes a quiz and all fail: 92 then the PercentCompliant would be 0

92/92 = 100%
It should be:
92/92 = 0%

please help and thanks

Bill Prew

Just a quick thought, thy this:

(Count(*) / Cast( SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END)  as float)) * 100 as PercentCompliant

Open in new window

»bp

Pawan Kumar

This should give you the result you need.

If all failed then this SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END) should return 0.

Check these-

SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END)
SUM( Case when ActionableResult = 0 THEN 1 ELSE 0 END)

SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as PercentCompliant

Open in new window

if you need only INT percentage

SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END) * 100 / COUNT(*) as PercentCompliant

Open in new window

Pawan Kumar

If all failed then this SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END) should return 0.

Check these-

SUM( Case when ActionableResult = 1 THEN 0 ELSE 1 END)
SUM( Case when ActionableResult = 0 THEN 1 ELSE 0 END)

Open in new window

SUM( Case when ActionableResult = 1 THEN 0 ELSE 1 END) * 100.0 / COUNT(*) as PercentCompliant

Open in new window

Amour22015

ASKER

I tried all and so far still comes up with 100%

If 10 Students take a test and all fail then the class would get a 0% grade. Not 100% That is the issue...

Bill Prew

Does ActionableResult mean they passed, or they failed?

»bp

Amour22015

ASKER

Maybe I need a case when statement:

Case When Count(*) = Cast( SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END)  as float) Then 0 
	Else Count(*) / Cast( SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END)  as float) *100 End as PerCompliant

Open in new window

Looks like this would work?

Bill Prew

Perhaps you need:

(Count(*) / Cast( SUM( Case when ActionableResult = 1 THEN 0 ELSE 1 END)  as float)) * 100 as PercentCompliant

Open in new window

»bp

Amour22015

ASKER

ActionableResult = 1 means they failed...

Bill Prew

Okay, then try my last code.

»bp

ASKER CERTIFIED SOLUTION

David Johnson, CD

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Amour22015

ASKER

So I ended up doing:

Cast( Count(*) - SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END)  as float) /Count(*) as PercentCompliant

Open in new window

thanks

Amour22015

ASKER

thank you all for helping

Bill Prew

This will get you the same result without an extra subtration arithmetic...

Cast((SUM(Case when ActionableResult = 1 THEN 0 ELSE 1 END)  as float) / Count(*) as PercentCompliant

Open in new window

»bp