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
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
While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
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 |
+---------+----------+
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
6 people are following this question.