Percentages

Question

Hello

I've got a list of Students in tbl1

and I need a query that returns the following report

It seemed straight forward when I started doing it, but I'm stuck in the mud with it

Any suggestions much appreciated

Thanks in advance Ron

Answer 1

Please try the following.
If you really need the % sign in the output, you can try to uncomment the commented line.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE 
(
    ID INT IDENTITY PRIMARY KEY
  , exam VARCHAR(20)
  , result VARCHAR(10)
);
INSERT INTO @tbl (exam, result) VALUES
('Math', 'Pass'),
('Math', 'Pass'),
('Math', 'Fail'),
('English', 'Pass'),
('English', 'Pass'),
('English', 'Pass'),
('Science', 'Fail'),
('Science', 'Fail'),
('Science', 'Pass');
-- DDL and sample data population, end

SELECT exam
     , FORMAT(SUM(IIF(result = 'Pass', 1, 0)) * 100.00 / COUNT(*), '###.##') AS PassRate
--, FORMAT(SUM(IIF(result = 'Pass', 1, 0)) * 1.00 / COUNT(*), '###.##%') AS PassRate
FROM @tbl
GROUP BY exam;

Output

+---------+----------+
|  exam   | PassRate |
+---------+----------+
| English |      100 |
| Math    |    66.67 |
| Science |    33.33 |
+---------+----------+

Answer 2

Please also check another method:

CREATE TABLE yourtable   
 (  
     ID  INT  
   , exam CHAR(10)  
   , result CHAR(10)  
 );  
 INSERT INTO yourtable VALUES  
 (1,'Math', 'Pass'),(2,'Math', 'Pass'),  
 (3,'Math', 'Fail'),(1,'English', 'Pass'),  
 (2,'English', 'Pass'),(3,'English', 'Pass'),  
 (1,'Science', 'Fail'),(2,'Science', 'Fail'),(3,'Science', 'Pass')  
  
;WITH cte   
AS(SELECT *,CASE WHEN result = 'Pass' THEN 1 ELSE 0 END num FROM yourtable)  
  
SELECT exam,FORMAT(SUM(num)*1.00/COUNT(*),'###.#0%') PassRate FROM cte  
GROUP BY exam  
  

Output:

exam  PassRate  
English   	100.00%  
Math      	66.67%  
Science   	33.33%  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table