Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | The Percentage Puzzle

In this puzzle you have find out the percentage of people passed in the exam and the percentage of the people failed the exam.

Please check out the sample input values and sample expected output below.

Sample Input

ID StudentName ActionableResult
1 Haverly 0
2 Due 1
3 Bourne 1
4 Pam 1

Expected Output

PercentPass PercentFail
25.00000000000 75.00000000000

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE StudentPassFail
(
	 ID INT
	,StudentName VARCHAR(10)
	,ActionableResult SMALLINT /* 1 - Fail , 0 - Pass */
)
GO

INSERT INTO StudentPassFail VALUES
( 1 , 'Haverly' , 0 ),
( 2 , 'Due' , 1 ),
( 3 , 'Bourne' , 1 ),
( 4 , 'Pam' , 1 )
GO


--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution – 1


--

SELECT 
	       ( COUNT(*)  - SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END )  ) * 100. / COUNT(*)  as PercentPass 
	,100 - ( COUNT(*)  - SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END )  ) * 100. / COUNT(*)  as PercentFail
FROM StudentPassFail
	
--

Output

--

/*------------------------
SELECT 
		   ( COUNT(*)  - SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END )  ) * 100. / COUNT(*)  as PercentPass 
	,100 - ( COUNT(*)  - SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END )  ) * 100. / COUNT(*)  as PercentFail
FROM StudentPassFail
------------------------*/
PercentPass                             PercentFail
--------------------------------------- ---------------------------------------
25.00000000000                          75.00000000000

(1 row(s) affected)


--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

Advertisements